Visualizzazione post con etichetta C# and SQL. Mostra tutti i post
Visualizzazione post con etichetta C# and SQL. Mostra tutti i post

giovedì 31 ottobre 2013

C# - SqlBulkCopy

SqlBulkCopy lets you efficiently bulk load a SQL Server table with data from another source.
For example if you need to synchronize differente Sql Server Tables or to put data from DataTable (or DataSource) to Sql Server Table in a very efficient way.

Here a Little Example:



public void UpdateTable(SqlConnection con, SqlTransaction tra, DataSet ds, string table)
        {
            string sql = "DELETE FROM " + table;

            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandText = sql;

            if (tra != null)
                com.Transaction = tra;

            com.ExecuteNonQuery();

            SqlCommand Command = new SqlCommand();
            Command.Connection = con;
            if (tra != null)
                Command.Transaction = tra;

            SqlBulkCopy copy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, tra);
            copy.DestinationTableName = table;
            copy.WriteToServer(ds.Tables[0]);
        }

with this cose, you can copy data from a ds to Sql Server Table. 

My Two Cents ...  

mercoledì 7 novembre 2012

Delete Commad with Null Parameter Value

Sometimes we need to make where conditions with null value.
If we use Command Paramenters the first thing we make is:

      
       SqlCommand.CommandText = "DELETE table WHERE field1 = @param1";

       SqlCommand.Parameters.Add(new SqlParameter("@param1", DBNull.Value)


Your problem is that you cannot use the '=' operator in SQL for null value.  
Instead, you need to generate different SQL for this case.
See the following example:


public void DeleteYourRecord(SqlConnection con, string param1, string param2, string param3)
        {
            SqlCommand com = new SqlCommand();
            com.Connection = con;

            com.Connection = con;
            com.CommandText = "DELETE table" +
                              "WHERE field1 = @param1 AND field2 = @param2 ";

            com.Parameters.Add(new SqlParameter("@param1", param1));
            com.Parameters.Add(new SqlParameter("@param2", param2));


            if (param3.Trim() == "")
            {
                com.CommandText += " AND field3 is null";
            }
            else
            {
                com.CommandText += " AND field3 = @param3";
                com.Parameters.Add(new SqlParameter("@param3", param3));
            }

            com.ExecuteNonQuery();
        }




My Two Cents...