[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8266
  • Last Modified:

How to use Prepared Statement in C#

Hi,

How we can use prepared Statement in C# to update Query

I am using .Net 2005

Kind Regards,
Dinesh
0
dinesh_bali
Asked:
dinesh_bali
  • 7
  • 5
  • 4
1 Solution
 
kraffayCommented:
Using connection As OleDbConnection = New _
        OleDbConnection(connectionString)
        connection.Open()

        ' Create the Command.
        Dim command As OleDbCommand = New OleDbCommand()

        ' Set the Connection, CommandText and Parameters.
        command.Connection = connection
        command.CommandText = _
          "INSERT INTO dbo.Region (RegionID, RegionDescription) VALUES (?, ?);"
        command.Parameters.Add("RegionID", OleDbType.Integer, 4)
        command.Parameters.Add("RegionDescription", OleDbType.VarWChar, 50)
        command.Parameters(0).Value = 20
        command.Parameters(1).Value = "First Region"

        ' Call  Prepare and ExecuteNonQuery.
        command.Prepare()
        command.ExecuteNonQuery()

        ' Change parameter values and call ExecuteNonQuery.
        command.Parameters(0).Value = 21
        command.Parameters(1).Value = "Second Region"
        command.ExecuteNonQuery()
    End Using
0
 
kraffayCommented:
That's actually an insert, but that will work with an update also
0
 
dinesh_baliAuthor Commented:
I do not think this is c# code.
Do we use Dim  in c#
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
kraffayCommented:
Oops, I pasted the wrong code from MSDN

ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.en/cpref4/html/M_System_Data_OleDb_OleDbCommand_Prepare.htm


private static void OleDbCommandPrepare(string connectionString)
{
    using (OleDbConnection connection = new
               OleDbConnection(connectionString))
    {
        connection.Open();

        // Create the Command.
        OleDbCommand command = new OleDbCommand();

        // Set the Connection, CommandText and Parameters.
        command.Connection = connection;
        command.CommandText =
            "INSERT INTO dbo.Region (RegionID, RegionDescription) VALUES (?, ?)";
        command.Parameters.Add("RegionID", OleDbType.Integer, 4);
        command.Parameters.Add("RegionDescription", OleDbType.VarWChar, 50);
        command.Parameters[0].Value = 20;
        command.Parameters[1].Value = "First Region";

        // Call  Prepare and ExecuteNonQuery.
        command.Prepare();
        command.ExecuteNonQuery();

        // Change parameter values and call ExecuteNonQuery.
        command.Parameters[0].Value = 21;
        command.Parameters[1].Value = "SecondRegion";
        command.ExecuteNonQuery();
    }
}

0
 
Gautham JanardhanCommented:
<code> using SQL Adpater ...u have to change that to oledb

using a sample table Employee, having Columns EMPID,EMPNAME,EMPADDRESS
EMPID is the primarykey


SQlAdapter =
                    new System.Data.SqlClient.SqlDataAdapter
                    ();
               SQlAdapter.SelectCommand =
                    (System.Data.SqlClient.SqlCommand)WALLET_DAL.SqlDbProvider.DataAccess.NewCommand
                    ("SELECT * FROM EMPLOYEE WHERE EMPID = @EMPID ");
               SQlAdapter.DeleteCommand =
                    (System.Data.SqlClient.SqlCommand)WALLET_DAL.SqlDbProvider.DataAccess.NewCommand
                    ("DELETE FROM EMPLOYEE WHERE EMPID = @EMPID ");
               SQlAdapter.UpdateCommand =
                    (System.Data.SqlClient.SqlCommand)WALLET_DAL.SqlDbProvider.DataAccess.NewCommand
                    ("UPDATE EMPLOYEE SET EMPNAME = @EMPNAME,"
                    +"EMPADDRESS = @EMPADDRESS WHERE EMPID = @EMPID ");
               SQlAdapter.InsertCommand =
                    (System.Data.SqlClient.SqlCommand)WALLET_DAL.SqlDbProvider.DataAccess.NewCommand
                    ("INSERT INTO EMPLOYEE (EMPID,EMPNAME,EMPADDRESS)VALUES("
                    +"@EMPID,@EMPNAME,@EMPADDRESS)");


System.Data.IDataParameter dtPrmTemp;
               foreach(System.Data.DataColumn ACol in ASet.Tables["EMPLOYEE"].Columns)
               {
                     
                    dtPrmTemp     = new SqlDataParameter();
                    dtPrmTemp.ParameterName = "@" + ACol.ColumnName;
                    //--- Bind Parameter to Column ---
                    dtPrmTemp.SourceColumn = ACol.ColumnName;
                    //--- Parameters of Param should take Current version ---
                    dtPrmTemp.SourceVersion = System.Data.DataRowVersion.Current;
                    SQlAdapter.InsertCommand.Parameters.Add(dtPrmTemp);
                }
//For Delete Command
                dtPrmTemp
                    =  new SqlDataParameter();
               dtPrmTemp.ParameterName = "@" + "EMPID";
               //--- Bind Parameter to Column ---
               dtPrmTemp.SourceColumn = "EMPID";
               //--- Parameters of Param should take Current version ---
               dtPrmTemp.SourceVersion = System.Data.DataRowVersion.Current;
          SQlAdapter.DeleteCommand.Parameters.Add(dtPrmTemp);

<end> similarly u will have to do for update command also..
0
 
dinesh_baliAuthor Commented:
Hi,

Thanks for your response.

kraffay: Yours example works fine. But your example is by using OleDb connection. which is basically used for Oracle.

Can we do this with  by using SqlCommand.

gauthampj : Your example is little hard for me, do not u have some easy way as kraffay has given the solution.

Thanks in advance

Regards
0
 
kraffayCommented:
Here you go:

    private static void SQLDbCommandPrepare(string connectionString)
    {
        using  (SqlConnection  connection = new
                   SqlConnection(connectionString))
        {
            connection.Open();

            // Create the Command.
            SqlCommand command = new SqlCommand();

            // Set the Connection, CommandText and Parameters.
            command.Connection = connection;
            command.CommandText =
                "INSERT INTO dbo.Region (RegionID, RegionDescription) VALUES (?, ?)";
            command.Parameters.Add("RegionID", SqlDbType.Int, 4);
            command.Parameters.Add("RegionDescription", SqlDbType.VarChar, 50);
            command.Parameters[0].Value = 20;
            command.Parameters[1].Value = "First Region";

            // Call  Prepare and ExecuteNonQuery.
            command.Prepare();
            command.ExecuteNonQuery();

            // Change parameter values and call ExecuteNonQuery.
            command.Parameters[0].Value = 21;
            command.Parameters[1].Value = "SecondRegion";
            command.ExecuteNonQuery();
        }
    }
0
 
dinesh_baliAuthor Commented:
Hi,

Thanks for trying to help me.

When I write
command.Prepare();

The exception comes saying that:
"Incorrect syntax near 'CTCT_PASSWORD'.\r\nIncorrect syntax near '?'.\r\nStatement(s) could not be prepared."

What is wrong?

Regards
0
 
dinesh_baliAuthor Commented:
Also,

like in upadate query we write string

update table set value = N'Unicode string'

Can we set value like this value in prepared statement.

Regards,
0
 
Gautham JanardhanCommented:
post ur code
0
 
dinesh_baliAuthor Commented:
DataCryptor cryptor = new DataCryptor(passwordCryptionKey);
                  
                  SqlConnection con = new DbConnection().Connection();
                  SqlConnection conToUpdate = new DbConnection().Connection();
                  SqlConnection conToInsert = new DbConnection().Connection();                                    
                  
                  SqlCommand command = new SqlCommand();
                  SqlCommand cmdToInsert = new SqlCommand();

                  command.Connection = conToUpdate;            
                  cmdToInsert.Connection = conToInsert;


                  command.CommandText =
                        "UPDATE CT_CUSTOMER SET CTCT_PASSWORD = ? WHERE CTCT_ID = ?";

                  cmdToInsert.CommandText =
                        "INSERT INTO CT_CUSTOMER_FAILED_PASSWORD (CTCT_ID, CTCT_PASSWORD, CTCT_EMAIL, CTCT_QUERY, DCOP_CULTURE) VALUES (?,?, ?, ?,?)";


                  command.Parameters.Add("CTCT_PASSWORD", SqlDbType.NVarChar, 200);
                  command.Parameters.Add("CTCT_ID", SqlDbType.NVarChar, 300);

                  cmdToInsert.Parameters.Add("CTCT_ID", SqlDbType.NVarChar, 300);
                  cmdToInsert.Parameters.Add("CTCT_PASSWORD", SqlDbType.NVarChar, 200);
                  cmdToInsert.Parameters.Add("CTCT_EMAIL", SqlDbType.NVarChar, 150);
                  cmdToInsert.Parameters.Add("CTCT_QUERY", SqlDbType.NVarChar, 2000);
                  cmdToInsert.Parameters.Add("DCOP_CULTURE", SqlDbType.NVarChar, 50);
                  
                  String queryText = "SELECT CS.CTCT_ID, CS.CTCT_EMAIL, CS.CTCT_PASSWORD, DOP.DCOP_CULTURE FROM CT_CUSTOMER CS"+
                        " INNER JOIN DC_DISTRIBUTIONCHANNELOPTIONS DOP ON DOP.CTCT_ID = CS.CTCT_ID "+
                        "INNER JOIN DC_DISTRIBUTIONCHANNEL DCH ON DCH.DCDC_ID = DOP.DCDC_ID "+
                        "INNER JOIN DC_DISTRIBUTIONCHANNELTYPE DTY ON DTY.DCDT_TYPE = DCH.DCDT_TYPE "+
                        "WHERE  DTY.DCDT_DESCRIPTION = 'VisitBritain'"+                        
                        "AND DOP.DCOP_CULTURE ='es-MX'";

                  SqlCommand objCmd = new SqlCommand(queryText, con);
                  objCmd.CommandTimeout = 250;      
            
                  String updateQuery = string.Empty;
                  String strID= string.Empty;      
                  String strPassword = string.Empty;
                  String strEmail = string.Empty;
                  String strCulture = string.Empty;

                  try
                  {                
                        SqlDataReader objRdr = objCmd.ExecuteReader();

                        while (objRdr.Read())
                        {
                              strID= objRdr["CTCT_ID"].ToString();
                              strPassword = objRdr["CTCT_PASSWORD"].ToString();
                              strEmail = objRdr["CTCT_EMAIL"].ToString();
                              strCulture = objRdr["DCOP_CULTURE"].ToString();

                              try
                              {
                                    strPassword = cryptor.Decrypt(strPassword);
                                    command.Parameters[0].Value = strPassword;
                                    command.Parameters[1].Value = strID;                                    
                                    command.Prepare();
                                    
                                    command.ExecuteNonQuery();                                    
                                    Pass += 1;
                              }
                              catch(Exception ex1)
                              {
                                    try
                                    {
                                          String err = ex1.Message.ToString();                                          

                                          cmdToInsert.Parameters[0].Value = strID;
                                          cmdToInsert.Parameters[1].Value = strPassword;
                                          cmdToInsert.Parameters[2].Value = strEmail;
                                          cmdToInsert.Parameters[3].Value = updateQuery;
                                          cmdToInsert.Parameters[4].Value = strCulture;      
                                          
                                          cmdToInsert.Prepare();
                                          cmdToInsert.ExecuteNonQuery();                                          
                                          Fail +=  1;
                                    }
                                    catch(Exception ex)
                                    {
                                          String err = ex.Message.ToString();
                                    }
                              }
                        }
                  }
                  catch (Exception ex)
                  {
                        String pass = updateQuery;
                        String str = ex.Message.ToString();
                  }
                  finally
                  {
                        if (con != null)
                        {
                              con.Close();
                        }
                        if(conToInsert != null)
                        {
                              conToInsert.Close();
                        }

                        if(conToUpdate != null)
                        {
                              conToUpdate.Close();
                        }                  
                  }            
0
 
dinesh_baliAuthor Commented:
Also, please guide how to add

update table set value = N'Unicode string'
 in prepared statement as said above.

Thanks in advance
0
 
Gautham JanardhanCommented:
change
<command.CommandText =
                    "UPDATE CT_CUSTOMER SET CTCT_PASSWORD = ? WHERE CTCT_ID = ?";

               cmdToInsert.CommandText =
                    "INSERT INTO CT_CUSTOMER_FAILED_PASSWORD (CTCT_ID, CTCT_PASSWORD, CTCT_EMAIL, CTCT_QUERY, DCOP_CULTURE) VALUES (?,?, ?, ?,?)";

>

to

command.CommandText =
                    "UPDATE CT_CUSTOMER SET CTCT_PASSWORD = @CTCT_PASSWORD  WHERE CTCT_ID = @CTCT_ID ";

               cmdToInsert.CommandText =
                    "INSERT INTO CT_CUSTOMER_FAILED_PASSWORD (CTCT_ID, CTCT_PASSWORD, CTCT_EMAIL, CTCT_QUERY, DCOP_CULTURE) VALUES (@CTCT_ID, @CTCT_PASSWORD, @CTCT_EMAIL, @CTCT_QUERY, @DCOP_CULTURE)";


<  command.Parameters.Add("CTCT_PASSWORD", SqlDbType.NVarChar, 200);
               command.Parameters.Add("CTCT_ID", SqlDbType.NVarChar, 300);

               cmdToInsert.Parameters.Add("CTCT_ID", SqlDbType.NVarChar, 300);
               cmdToInsert.Parameters.Add("CTCT_PASSWORD", SqlDbType.NVarChar, 200);
               cmdToInsert.Parameters.Add("CTCT_EMAIL", SqlDbType.NVarChar, 150);
               cmdToInsert.Parameters.Add("CTCT_QUERY", SqlDbType.NVarChar, 2000);
               cmdToInsert.Parameters.Add("DCOP_CULTURE", SqlDbType.NVarChar, 50);
>

to
  command.Parameters.Add("@CTCT_PASSWORD", SqlDbType.NVarChar, 200);
               command.Parameters.Add("@CTCT_ID", SqlDbType.NVarChar, 300);

               cmdToInsert.Parameters.Add("@CTCT_ID", SqlDbType.NVarChar, 300);
               cmdToInsert.Parameters.Add("@CTCT_PASSWORD", SqlDbType.NVarChar, 200);
               cmdToInsert.Parameters.Add("@CTCT_EMAIL", SqlDbType.NVarChar, 150);
               cmdToInsert.Parameters.Add("@CTCT_QUERY", SqlDbType.NVarChar, 2000);
               cmdToInsert.Parameters.Add("@DCOP_CULTURE", SqlDbType.NVarChar, 50);


0
 
Gautham JanardhanCommented:
u should also change the places where u refer to this parametrer using the new name

like 'CTCT_ID' to '@CTCT_ID'
0
 
Gautham JanardhanCommented:
<update table set value = N'Unicode string'
>
i think u dont have to do anything special to achieve this because u have said that ur datatype is sql n varchar
 here <cmdToInsert.Parameters.Add("@CTCT_ID", SqlDbType.NVarChar, 300);
>
0
 
dinesh_baliAuthor Commented:
Thanks allot.

Finally it works as I wished.

Thanks to all of you who tried to help me

Cheers!

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 7
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now