grid is populated with data and user has option to edit records or to add new records and save all his/her changes in a single click on Save button. How do you save the data in the database?
.net sqlBulkCopy class. Later they will
realize that the login used in the application
to connect the database server does not have sufficient permission to perform
bulk copy operation.On approaching the DBA, he will raise his concern on changing the security settings of the login.
handle this scenario, but I will be concentrating on the new feature in SQL
server 2008 called Table Valued Parameter.Let us assume that the screes
loading a grid with two columns, for example county
code ,Country name.
process and not the efficient way of doing.This method might creates lots of blocking in a busy table as the
length of transaction increase based on the number of records.
parameter as comma separated values and use another delimiter (may be
a semicolon) as a record delimiter. For
example pass the below string as a parameter to the procedure.
parse the string using UDF to a table variable and insert/update the target table . This method is very conventional
way and work with any version of SQL Server. In SQL server there is no in built in function to parse the string and string parsing in costly operation as it is not a set operation. This method might work well with small number of records.It will become
worst when the number of records increase.Implementation of this method also not very straight forward. In the application, we have to write a custom code to make the records in grid to a string. In the procedure also we have to write user defined function(UDF) to parse the string.
server 2005 onwards it support XML data type.The XML representation of the data
is passed into the procedure. Inside the procedure the XML is parsed using
XQuery or sp_xm_preparedocument/OPENXML and insert/update the target table.This option has performance improvement over multiple procedure call but the performance might affect when the number of records go beyond 5000.Many people will be reluctant to implement this method as they are not much familiar with the XML and XQuery.
perspective this is very impressive. Easy to code and implement . This implementation
requires explicit insert/update permission on the target table for the login
used to connect the database. Otherwise the login/user should be member of
db_datawtiter database role. Moreover by default this operation will disable
the check constraints and triggers on the table.In other words,by default the trigger associted with the table will not get fired while inserting/updating the records using this method. To disable the check constraints and triggers, the login required
explicit alter permission on the table. If you try to perform sqlBulkCopy on a table which has check constraint/trigger, it will throw an error as given below(Assuming that the login does not have permission to alter the table).
trigger options should be enabled while calling the SQLbulkcopy method in the application code. This
option is quiet fast and efficient in the scenario where we need to
insert/update more number of records like loading the table
from huge excel or csv file.
This is as a new feature in SQL server to pass the data table (Table variable)
to a store procedure from SQL code or from the application. As per the MSDN
documentation
is comparable to other ways of using set-based variables; however, using
table-valued parameters frequently can be faster for large data sets. Compared
to bulk operations that have a greater startup cost than table-valued parameters,
table-valued parameters perform well for inserting less than 1000 rows.
”The implementation of this method is very straight forward and easy to maintain. Let us see the implementation of Table Valued Parameter. There are some
restriction in the usage of Table Valued Parameters.
- Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.
- You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure.
Let us create a table,user defined table types and a procedure.Application call this procedure to insert/update record into the target table.
--Table creation
CREATE TABLE Country_lkp(
CountryCode CHAR(3) NOT NULL,
CountryName VARCHAR(50) NOT NULL
)GOALTER TABLE Country_lkp ADD CONSTRAINT Pk_Country_lkp PRIMARY KEY(CountryCode)GO--Creating user defined data typeCREATE TYPE Country AS TABLE(CountryCode CHAR(3) , CountryName VARCHAR(50) )GO--The procedure to call from the applicationCREATE PROCEDURE Insert_Country_lkp( @Countrylist AS Country READONLY)AS
BEGIN
INSERT INTO Country_lkp (CountryCode,CountryName) SELECT * FROM @Countrylist END
User defined table types is a new feature in SQL server 2008 to pass multiple records to a SQL routine from SQL code or from the application. After executing the above script, you can see the use defined table data type in object explorer as given below.
dtCountry.Columns.Add("Code", typeof(string));
dtCountry.Columns.Add("Name", typeof(string));
DataRow dr = dtCountry.NewRow();
dr["Code"] = "IN";
dr["Name"] = "INDIA";
dtCountry.Rows.Add(dr);
dr = dtCountry.NewRow();
dr["Code"] = "CN";
dr["Name"] = "CHINA";
dtCountry.Rows.Add(dr);
dr = dtCountry.NewRow();
dr["Code"] = "US";
dr["Name"] = "UNITED STATE";
dtCountry.Rows.Add(dr);
string connString = "Server=MyServer;user id=" + "LOGINNAME" + ";password=" + "PASSWORD" + ";database=MYDATABASE;packet size=4096";
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("Insert_Country_lkp", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Countrylist", dtCountry);
cmd.ExecuteNonQuery();
}
}
After executing this code,you can see three records in the country_lkp table.
If you liked this post, do like my page on FaceBook
