This may seem trivial but every example I've ever seen of ADO.net almost always has a 'fill' before an 'update'. We really don't want to fill a dataset that may have 1000's of blobs, we just want to add(insert) to the table. Is fill required for an update?As an example here's sample code fro开发者_如何学Pythonm MSFT's web site (we're doing something similar):
SqlConnection con = new SqlConnection("Server=Darkover;uid=<username>;pwd=<strong password>;database=northwind");
SqlDataAdapter da = new SqlDataAdapter("Select * From MyImages", con);
SqlCommandBuilder MyCB = new SqlCommandBuilder(da); // What does this even do?
DataSet ds = new DataSet("MyImages");
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
FileStream fs = new FileStream(@"C:\winnt\Gone Fishing.BMP", FileMode.OpenOrCreate, FileAccess.Read);
byte[] MyData= new byte[fs.Length];
fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));
fs.Close();
da.Fill(ds,"MyImages"); // are they really filling a dataset with all those images???
DataRow myRow;
myRow=ds.Tables["MyImages"].NewRow();
myRow["Description"] = "This would be description text";
myRow["imgField"] = MyData;
ds.Tables["MyImages"].Rows.Add(myRow);
da.Update(ds, "MyImages");
con.Close();
You want the call to da.Fill()
in order to get the schema for the MyImages
table. That way you can use the DataRow
returned by the call to NewRow()
with the correct schema (columns, keys, etc) when assigning values to the columns.
You can tell the SqlDataAdapter
to return only the schema with no data by setting the SqlDataAdapter.FillCommandBehavior
:
da.FillCommandBehavior = CommandBehavior.SchemaOnly;
da.Fill(ds,"MyImages"); // Just get the schema for MyImages
DataRow myRow = ds.Tables["MyImages"].NewRow();
myRow["Description"] = "This would be description text";
myRow["imgField"] = MyData;
ds.Tables["MyImages"].Rows.Add(myRow);
da.Update(ds, "MyImages");
You can alternatively use da.FillSchema()
, with either a single DataTable
or a DataSet
if your query returns multiple tables.
DataTable dt = new DataTable();
da.FillSchema(dt, SchemaType.Mapped);
DataRow myRow = dt.NewRow();
myRow["Description"] = "This would be description text";
myRow["imgField"] = MyData;
dt.Rows.Add(myRow);
da.Update(dt);
精彩评论