Adding SqlCommand parameters in C#-CodePudding

Please help me to add paramaters in this queryToDB SqlCommand

this is the code

cons = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
cons.Open();

SqlCommand queryToDB = new SqlCommand("INSERT INTO [dbo].[FileUploadDBs] values (@Name, @Path, @Blasted, @CreatedBy, @CreatedDate)", cons);

//param.ParameterName  "@Name";
//param.Value = prefix   extension;
//string queryToDB = "INSERT INTO [dbo].[FileUploadDBs] values (@Name, @Path, @Blasted, @CreatedBy, @CreatedDate)";
connExcel.Open();
cmdExcel.CommandText =
    "SELECT NAMA , REPLACE(NOHP, '-', '' ) as NOHP,TANGGAL, NOMINAL  From ["   sheetName  
    "] WHERE NAMA IS NOT NULL OR NoHP IS NOT NULL OR Tanggal IS NOT NULL OR NOMINAL IS NOT NULL";
odaExcel.SelectCommand = cmdExcel;
//dapetin data dimasukin ke dtSheet
odaExcel.Fill(dtSheet);

connExcel.Close();

I want to add parameters :

@Name = prefix extension
@Path = filepath extension
@Blasted = 0
@CreatedBy = user
@CreatedDate = DateTime.Now()

I have no idea to use SqlCommand because it's first time using this. Thanks :)

CodePudding user response：

In addition to M4N, when doing an insert, you need to explicitly state the columns you are inserting AND the values. something like

var queryToDB = new SqlCommand(
@"INSERT INTO [dbo].[FileUploadDBs] 
     ( Name, Path, Blasted, CreatedBy, CreateDate )
     values 
     (@Name, @Path, @Blasted, @CreatedBy, @CreatedDate)", cons);

CodePudding user response：

There are different ways to add parameters to an SqlCommand, e.g:

command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;

command.Parameters.AddWithValue("@demographics", demoXml);

so for example, you could use this:

queryToDB.Parameters.AddWithValue("@Name", prefix   extension);

CodePudding user response：

Use the AddWithValue method of SqlCommand https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparametercollection.addwithvalue?view=dotnet-plat-ext-6.0

    cons = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
    cons.Open();

    SqlCommand queryToDB = new SqlCommand("INSERT INTO [dbo].[FileUploadDBs] values (@Name, @Path, @Blasted, @CreatedBy, @CreatedDate)", cons);

    queryToDB.Parameters.AddWithValue("@Name",prefix extension);
    queryToDB.Parameters.AddWithValue("@Path", filepath extension);
    queryToDB.Parameters.AddWithValue("@Blasted", 0);
    queryToDB.Parameters.AddWithValue("@CreatedBy", user);
    queryToDB.Parameters.AddWithValue("@CreatedDate", DateTime.Now());

    //param.ParameterName  "@Name";
    //param.Value = prefix   extension;
    //string queryToDB = "INSERT INTO [dbo].[FileUploadDBs] values (@Name, @Path, @Blasted, @CreatedBy, @CreatedDate)";
    connExcel.Open();
    cmdExcel.CommandText =
        "SELECT NAMA , REPLACE(NOHP, '-', '' ) as NOHP,TANGGAL, NOMINAL  From ["   sheetName  
        "] WHERE NAMA IS NOT NULL OR NoHP IS NOT NULL OR Tanggal IS NOT NULL OR NOMINAL IS NOT NULL";
    odaExcel.SelectCommand = cmdExcel;
    //dapetin data dimasukin ke dtSheet
    odaExcel.Fill(dtSheet);

    connExcel.Close();