Solved

How to use Prepared Statement in C#

Posted on 2006-11-09
16
7,738 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

636 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