Solved

How to use Prepared Statement in C#

Posted on 2006-11-09
16
7,482 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now