locked
How SqlParameter[] ParamArr in the below code works RRS feed

  • Question

  • User-1499457942 posted

    Hi

    SqlParameter[] ParamArr = new SqlParameter[2];
                                ParamArr[0] = new SqlParameter("@SDate", Convert.ToDateTime(txt_SDate.Text.ToString()));
                                ParamArr[1] = new SqlParameter("@CDate", Convert.ToDateTime(txt_CDate.Text.ToString()));
    
                                string query = "INSERT INTO [Period] (SDate,CDate) VALUES (@SDate,@CDate)";
    if (SqlDbOperation.RecordNavigation(query, ParamArr, SrcType.SqlQuery) == true)
    {
    string message = "Done !";
    ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + message + "');", true);
    } public static bool RecordNavigation(string SrcSql, SqlParameter[] paramArr, SrcType SourceType = SrcType.SqlQuery) { SqlCommand comm = new SqlCommand(); try { comm.CommandText = SrcSql; if (SourceType == SrcType.SqlQuery) comm.CommandType = CommandType.Text; else if (SourceType == SrcType.Table) comm.CommandType = CommandType.TableDirect; comm.Connection = new SqlConnection(CommonFunction.connectionString); foreach (SqlParameter param in paramArr) comm.Parameters.Add(param); comm.Connection.Open(); if (comm.Connection.State == ConnectionState.Open) { if (comm.ExecuteNonQuery() > 0) return true; } } catch (Exception ex) { } finally { comm.Parameters.Clear(); comm.Connection.Close(); } return false; }

    Thanks

    Monday, December 24, 2018 10:36 AM

All replies

  • User753101303 posted

    Hi,

    This is just an array. Then you copy each array element to your SqlCommand.Parameters. And so behind the scene it sends to SQL Server a statement with parameters the value to use for each parameter.

    Parameters are handled on the server side. They are not just replaced in a string, they are a feature of SQL Server (and most if not all other databases).

    I remember you had problem on this previously. if it doesn't help try perhaps to explain what you find weird so that we can better understand which point needs to be clarified.

    If I remember at first you were confusing C# declared variables and SQL parameter names. SQL knows nothing about your C# code.

    Monday, December 24, 2018 11:27 AM
  • User-1499457942 posted

    Hi

      I want to kbnow how the below 3 lines work

    ParamArr[0] = new SqlParameter("@SDate", Convert.ToDateTime(txt_SDate.Text.ToString()));
                                ParamArr[1] = new SqlParameter("@CDate", Convert.ToDateTime(txt_CDate.Text.ToString()));
    
                                string query = "INSERT INTO [Period] (SDate,CDate) VALUES (@SDate,@CDate)";

    Thanks
    Monday, December 24, 2018 11:35 AM
  • User475983607 posted

    Hi

      I want to kbnow how the below 3 lines work

    ParamArr[0] = new SqlParameter("@SDate", Convert.ToDateTime(txt_SDate.Text.ToString()));
                                ParamArr[1] = new SqlParameter("@CDate", Convert.ToDateTime(txt_CDate.Text.ToString()));
    
                                string query = "INSERT INTO [Period] (SDate,CDate) VALUES (@SDate,@CDate)";
    
    Thanks

    First, we'll assume txt_SDate.Text and txt_CDate.Text contain the string values; 12/24/2018 and 12/25/2018.  Keep in mind that you are not validating the inputs which is a recommendation we have made in most all you parameterized query posts. 

    The first two lines write SQL script similar to...

    DECALRE @SDate AS DateTime
    SET @SDate = '12/24/2018'
    
    DECALRE @CDate AS DateTime
    SET @CDate = '12/25/2018'

    The third line is the writes as is...

    INSERT INTO [Period] (SDate,CDate) VALUES (@SDate,@CDate)

    All together...

    DECALRE @SDate AS DateTime
    SET @SDate = '12/24/2018'
    
    DECALRE @CDate AS DateTime
    SET @CDate = '12/25/2018'
    
    INSERT INTO [Period] (SDate,CDate) VALUES (@SDate,@CDate)

    This assumes there are no validation errors.

    Monday, December 24, 2018 11:48 AM
  • User753101303 posted

    If this is not your own code, you could start with a test sample that uses jus the basic. For example https://csharp-station.com/Tutorial/AdoDotNet/Lesson06

    There is really nothing special. You just assign to an array a description of each parameter (name and value). In your SQL statement you are using the same named parameters. This SQL statement runs using a SqlCommand object and this array is used to populate SqlCommand.Parameters. ADO.NET will then use https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017 to run this command with the needed parameters.

    This code is a thin wrapper around that to avoid having to type the same code again and again (tough IMO it could even be a bit more generic)

    Monday, December 24, 2018 11:58 AM
  • User-893317190 posted

    Hi JagjitSingh,

    If you have sql server profiler( through sql server profiler, you could see what sql ado.net sends to sql server), you could see what sql server really executes  when your ado.net code runs.

    For example , if I  write the code below.

      using (SqlCommand com = new SqlCommand("INSERT INTO [Period] (Date1, Date2) VALUES(@StartDate, @ClosingDate)", con))
                    {
                        SqlParameter[] ParamArr = new SqlParameter[2];
                        ParamArr[0] = new SqlParameter("@StartDate", Convert.ToDateTime("2015-09-12"));
                        ParamArr[1] = new SqlParameter("@ClosingDate", Convert.ToDateTime("2016-02-13"));
    
                        com.Parameters.AddRange(ParamArr);
                       con.Open();
                        com.ExecuteNonQuery();
                       
                        com.ExecuteNonQuery();
                    }

    Finally , sql server will execute the sql below.

    exec sp_executesql N'INSERT INTO [Period] (Date1, Date2) VALUES(@StartDate, @ClosingDate)', -- first part 
    N'@StartDate datetime,@ClosingDate datetime', -- second part
    @StartDate='2015-09-12 00:00:00',@ClosingDate='2016-02-13 00:00:00' -- third part

    You could find corresponding elements in your c# code.

    The sql's first part is the sql in the c# code. "INSERT INTO [Period] (Date1, Date2) VALUES(@StartDate, @ClosingDate)".

    The sql's second part is the name  and their type  of sqlparameter.

    The third part is the value of the sqlparameter.

    This is how the c# code is converted to sql server's sql.

    Best regards,

    Ackerly Xu

    Tuesday, December 25, 2018 3:22 AM