Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8627
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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