Insert dataset into database
-
Hi everyone How to insert the records in the dataset into the database. i tried the adapter.update(ds). but it does not work... thank you for reading zheng
DataAdapters, and Update in particular, are very limited in what they can do -- I avoid them. I've forgotten the details, but generally (for SQL Server): 0) Set a Command : INSERT INTO table (field0 ... ) VALUES (@Param0 ... ) 1) Create the parameters : cmd.Parameters.Add ( new SqlParameter ( "@Param0" , typeof(whatever) ) ) 2) Iterate the rows of a DataTable : foreach ( DataRow dr in ds.Table [ i ].Rows ) 2.1) Set the parameter values : cmd.Parameters [ 0 ].Value = dr [ 0 ] ... 2.2) Execute the cmd : cmd.ExecuteNonQuery() 3) Repeat as necessary I prefer to encapsulate this into a method so I don't need to remember it.
-
DataAdapters, and Update in particular, are very limited in what they can do -- I avoid them. I've forgotten the details, but generally (for SQL Server): 0) Set a Command : INSERT INTO table (field0 ... ) VALUES (@Param0 ... ) 1) Create the parameters : cmd.Parameters.Add ( new SqlParameter ( "@Param0" , typeof(whatever) ) ) 2) Iterate the rows of a DataTable : foreach ( DataRow dr in ds.Table [ i ].Rows ) 2.1) Set the parameter values : cmd.Parameters [ 0 ].Value = dr [ 0 ] ... 2.2) Execute the cmd : cmd.ExecuteNonQuery() 3) Repeat as necessary I prefer to encapsulate this into a method so I don't need to remember it.
hi thank you for reply! below is my code: string SelectSchema = "SELECT * FROM [Package Data Range]"; DataSet ds = new DataSet(); OleDbDataAdapter adapterPkInfo = new OleDbDataAdapter(SelectSchema, ImportCon); OleDbCommandBuilder cmdBldr = new OleDbCommandBuilder(adapterPkInfo); adapterPkInfo.FillSchema(ds, SchemaType.Source, "PackageDataRangeSchema"); DataSet ds2 = new DataSet(); string selectPkInfo = "SELECT * FROM [Package Data Range]"; OleDbDataAdapter adapter = new OleDbDataAdapter(selectPkInfo, connection); adapter.Fill(ds2, "Package Data Range"); ds.Tables[0].Merge(ds2.Tables[0],true,MissingSchemaAction.Ignore); my dataet has around 200 columns. it is not efficient to repeat that.... thank you :) zheng
-
hi thank you for reply! below is my code: string SelectSchema = "SELECT * FROM [Package Data Range]"; DataSet ds = new DataSet(); OleDbDataAdapter adapterPkInfo = new OleDbDataAdapter(SelectSchema, ImportCon); OleDbCommandBuilder cmdBldr = new OleDbCommandBuilder(adapterPkInfo); adapterPkInfo.FillSchema(ds, SchemaType.Source, "PackageDataRangeSchema"); DataSet ds2 = new DataSet(); string selectPkInfo = "SELECT * FROM [Package Data Range]"; OleDbDataAdapter adapter = new OleDbDataAdapter(selectPkInfo, connection); adapter.Fill(ds2, "Package Data Range"); ds.Tables[0].Merge(ds2.Tables[0],true,MissingSchemaAction.Ignore); my dataet has around 200 columns. it is not efficient to repeat that.... thank you :) zheng
shoubi wrote:
it is not efficient to repeat that
Indeed, but as long as you want to insert all of them, you can loop through the columns and cobble up the Insert statement and parameters.
-
shoubi wrote:
it is not efficient to repeat that
Indeed, but as long as you want to insert all of them, you can loop through the columns and cobble up the Insert statement and parameters.
-
yup. thank you very much! however just want to check with you the "adapter.update()" how does it work? does it require any commands? or it is smart enough to act accordingly? thank you!:)
It requires a SELECT command from which it (actually a CommandBuilder) can produce INSERT, UPDATE, and DELETE commands. Generally, the data must involve one table, have a primary key, and not contain duplicates.
-
It requires a SELECT command from which it (actually a CommandBuilder) can produce INSERT, UPDATE, and DELETE commands. Generally, the data must involve one table, have a primary key, and not contain duplicates.