none
Update the record if exists in database RRS feed

  • Question

  • Hi All,

    I am importing data from CSV file to my sql server database.

    I successfully get data into datatable and using the SqlBulkCopy method to import data into database.

    It's working fine.

    But i want that if record id is exists then update the record do not insert that record.

    Any body have any idea.

    Thanks in advance.
    Monday, January 24, 2011 6:09 PM

Answers

  • Hi Anuj,

    This is my source code and i want you to compile it.

    DB:

    UserName nvarchar(10) primary key

    Password nvarchar(10)

    Code:

     class Program
        {
            static void Main(string[] args)
            {

                DataTable table = null;
                using (SqlConnection conn = new SqlConnection(Help.ConnectString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand("select * from UserInfo", conn);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    table = ds.Tables[0];
                }

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    table.Rows[i][1] = "Microsoft";
                }

                using (SqlConnection conn = new SqlConnection(Help.ConnectString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.UpdateCommand = new SqlCommand("update userinfo set password=@password where username=@username", conn);
                    //In the code sample, the username is the primary key, so it will keep unique and unable to update(may be you insert a new one).
                    //if you have multiples columns to update, you can set  the query such as update userinfo set
    password1=@password, password2=@password2 where username=@username
                    //
                    adapter.UpdateCommand.Parameters.Add("@username", SqlDbType.NChar, 10, "UserName");
                    adapter.UpdateCommand.Parameters.Add("@password", SqlDbType.NChar, 10, "Password");
                    DataRow newRow = table.NewRow();
                    newRow["UserName"] = "MSDNA";
                    newRow["Password"] = "SUPPORTER";
                    DataRow newRow1 = table.NewRow();
                    newRow1["UserName"] = "MSDN";
                    newRow1["Password"] = "SUPPORTER";
                    table.Rows.Add(newRow1);
                    table.Rows.Add(newRow);
                    adapter.InsertCommand = new SqlCommand("insert into userinfo values(@username,@password)", conn);
                    adapter.InsertCommand.Parameters.Add("@username", SqlDbType.NChar, 10, "UserName");
                    adapter.InsertCommand.Parameters.Add("@password", SqlDbType.NChar, 10, "Password");
                    adapter.Update(table);

                }

                Console.Read();
            }
        }

    Best Regards!

     


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 28, 2011 12:45 PM
    Moderator
  • Hi Anuj,

    There is a tool that you could use to monitor the database "SQL Server Profier" and  I belive you can do it by yourself.

    Anyway enjoy you weekend. ~me too hahaha..

    David

     


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Pratap09 Thursday, February 3, 2011 9:37 AM
    • Unmarked as answer by Pratap09 Thursday, February 3, 2011 9:37 AM
    • Marked as answer by Pratap09 Thursday, February 3, 2011 9:37 AM
    Friday, January 28, 2011 1:01 PM
    Moderator

All replies


  • Hi Anuj,

    Welcome to ADO.NET DataSet forum.
    As far as I know the sqlbulkcopy class can be used to write data only.
    I suggest you as the follows:

                    using (SqlConnection conn = new SqlConnection(Help.ConnectString))
                    {
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.UpdateCommand = new SqlCommand("update userinfo set password=@password where username=@username", conn);
                        adapter.UpdateCommand.Parameters.Add("@username", SqlDbType.NChar, 10, "UserName");
                        adapter.UpdateCommand.Parameters.Add("@password", SqlDbType.NChar, 10, "Password");

                       
                        adapter.InsertCommand = new SqlCommand("insert into userinfo values(@username,@password)", conn);
                        adapter.InsertCommand.Parameters.Add("@username", SqlDbType.NChar, 10, "UserName");
                        adapter.InsertCommand.Parameters.Add("@password", SqlDbType.NChar, 10, "Password");


                        adapter.Update(table);

                    }
        
    Addition, there is the description of sqlbulkcopy http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx.
    I hope that would be help you, and if you had any other forward movement please let me know.
    Regards!

    David

     

    Wednesday, January 26, 2011 12:57 AM
    Moderator
  • Hi David,

    Thanks for reply as you suggested above.

    I have to execute query for every row of csv file, i want insert and update record on a single hit.

    I don't want to connect database for every row.

    Thursday, January 27, 2011 5:11 PM
  • Hi Anuj,

    Welcome to coming back!

     


    This is the result that i had track the database with the update command, and we will find that there is only one

    connection with the database, the command had post all the query at the same time. So define the update and

    insert command first, then add all the date in the table and run the update command.

    I hope that could be help you and if you have any prbolem please let me know.

    Have a good day.

    Regards!


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 28, 2011 2:23 AM
    Moderator
  • Hi David,

    Thanks for quick reply..

    As you mention above, you are updating and inserting one record.

    Can you please tell me how i do for a lot of records?

    I am unable to understand how i add multiple parameters in a single command for all the records.

    Can you please give a sample example to traverse through the CSV file and add insert and update parameters to command for all the records.

    Thanks in advance.

     

    Friday, January 28, 2011 9:21 AM
  • Hi Anuj,

    This is my source code and i want you to compile it.

    DB:

    UserName nvarchar(10) primary key

    Password nvarchar(10)

    Code:

     class Program
        {
            static void Main(string[] args)
            {

                DataTable table = null;
                using (SqlConnection conn = new SqlConnection(Help.ConnectString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand("select * from UserInfo", conn);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    table = ds.Tables[0];
                }

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    table.Rows[i][1] = "Microsoft";
                }

                using (SqlConnection conn = new SqlConnection(Help.ConnectString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.UpdateCommand = new SqlCommand("update userinfo set password=@password where username=@username", conn);
                    //In the code sample, the username is the primary key, so it will keep unique and unable to update(may be you insert a new one).
                    //if you have multiples columns to update, you can set  the query such as update userinfo set
    password1=@password, password2=@password2 where username=@username
                    //
                    adapter.UpdateCommand.Parameters.Add("@username", SqlDbType.NChar, 10, "UserName");
                    adapter.UpdateCommand.Parameters.Add("@password", SqlDbType.NChar, 10, "Password");
                    DataRow newRow = table.NewRow();
                    newRow["UserName"] = "MSDNA";
                    newRow["Password"] = "SUPPORTER";
                    DataRow newRow1 = table.NewRow();
                    newRow1["UserName"] = "MSDN";
                    newRow1["Password"] = "SUPPORTER";
                    table.Rows.Add(newRow1);
                    table.Rows.Add(newRow);
                    adapter.InsertCommand = new SqlCommand("insert into userinfo values(@username,@password)", conn);
                    adapter.InsertCommand.Parameters.Add("@username", SqlDbType.NChar, 10, "UserName");
                    adapter.InsertCommand.Parameters.Add("@password", SqlDbType.NChar, 10, "Password");
                    adapter.Update(table);

                }

                Console.Read();
            }
        }

    Best Regards!

     


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, January 28, 2011 12:45 PM
    Moderator
  • Hi Anuj,

    There is a tool that you could use to monitor the database "SQL Server Profier" and  I belive you can do it by yourself.

    Anyway enjoy you weekend. ~me too hahaha..

    David

     


    If it's helpful for you, Please vote or mark. Thank you!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Pratap09 Thursday, February 3, 2011 9:37 AM
    • Unmarked as answer by Pratap09 Thursday, February 3, 2011 9:37 AM
    • Marked as answer by Pratap09 Thursday, February 3, 2011 9:37 AM
    Friday, January 28, 2011 1:01 PM
    Moderator
  • Thanks a ton David........

    Your code is working..

    I have modified code to use command builder to generate insert and update query.

     DataTable table = null;
                using (SqlConnection conn = new SqlConnection(connection))
                {
                     SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand("select * from UserInfo", conn);
                    SqlCommandBuilder Bldr = null;
                    Bldr = new SqlCommandBuilder(adapter);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    table = ds.Tables[0];
             

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    table.Rows[i][1] = "Microsoft";
                }

                    DataRow newRow = table.NewRow();
                    newRow["UserName"] = "MSDNA";
                    newRow["Password"] = "SUPPORTER";
                    DataRow newRow1 = table.NewRow();
                    newRow1["UserName"] = "MSDN";
                    newRow1["Password"] = "SUPPORTER";
                    table.Rows.Add(newRow1);
                    table.Rows.Add(newRow);
                  
                    adapter.Update(table);
                }

     

    Thursday, February 3, 2011 9:38 AM
  • Hi David,

    I am using the given code to insert update.

    It's woring for insert but not updating the record

    public void UpdateDatatable(string ProcedureName, Hashtable Parameters, DataTable Table)
            {
                //Creating instance of sqladapter class to update the datatable in a single command.
                SqlConnection Connection = null;
                SqlDataAdapter adapter = null;
                SqlCommandBuilder Bldr = null;

                try
                {
                    Connection = new SqlConnection(ConnectionString);
                    Connection.Open();
                    adapter = new SqlDataAdapter(ProcedureName, Connection);
                    if (Parameters != null && Parameters.Count > 0)
                    {
                        //Add parameters to select command
                        foreach (DictionaryEntry dEntry in Parameters)
                        {
                            SqlParameter spParam = new SqlParameter(dEntry.Key.ToString(), dEntry.Value);
                            adapter.SelectCommand.Parameters.Add(spParam);
                        }
                    }
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    Bldr = new SqlCommandBuilder(adapter);
                    //Update the datatable 
                    adapter.Update(Table);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    //Disposing all the used connection resources
                    if (adapter != null)
                        adapter.Dispose();
                    if (Connection != null)
                    {
                        if (Connection.State == ConnectionState.Open)
                            Connection.Close();
                        Connection.Dispose();
                    }
                    if (Bldr != null)
                        Bldr.Dispose();
                }
            }

    what wrong i doing in above code.

    Thursday, February 3, 2011 11:47 AM