Solved

How to use Prepared Statement in C#

Posted on 2006-11-09
16
7,549 Views
Last Modified: 2012-06-27
Hi,

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

I am using .Net 2005

Kind Regards,
Dinesh
0
Comment
Question by:dinesh_bali
  • 7
  • 5
  • 4
16 Comments
 
LVL 9

Expert Comment

by:kraffay
ID: 17910872
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
 
LVL 9

Expert Comment

by:kraffay
ID: 17910874
That's actually an insert, but that will work with an update also
0
 

Author Comment

by:dinesh_bali
ID: 17914630
I do not think this is c# code.
Do we use Dim  in c#
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 9

Expert Comment

by:kraffay
ID: 17915711
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
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17924234
<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
 

Author Comment

by:dinesh_bali
ID: 17933468
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
 
LVL 9

Expert Comment

by:kraffay
ID: 17933580
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
 

Author Comment

by:dinesh_bali
ID: 17937030
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
 

Author Comment

by:dinesh_bali
ID: 17937106
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
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17937121
post ur code
0
 

Author Comment

by:dinesh_bali
ID: 17937243
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
 

Author Comment

by:dinesh_bali
ID: 17937248
Also, please guide how to add

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

Thanks in advance
0
 
LVL 29

Accepted Solution

by:
Gautham Janardhan earned 250 total points
ID: 17937320
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
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17937322
u should also change the places where u refer to this parametrer using the new name

like 'CTCT_ID' to '@CTCT_ID'
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17937333
<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
 

Author Comment

by:dinesh_bali
ID: 17939364
Thanks allot.

Finally it works as I wished.

Thanks to all of you who tried to help me

Cheers!

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question