store a c# byte array
-
hello forum, i want to store c# objects in a MS SQL database. I think the best way is to serialize the object to a byte array but i have problems to insert a c# byte array into the database... First, the equivalent type of datum in MS SQL is the binary or varbinary type, isn´t it? well, my code:
MyObject mo = new MyObject(); MemoryStream ms= new MemoryStream(); BinaryFormatter bf = new BinaryFormatter(); b.Serialize(ms,mo); SqlConnection sc = new SqlConnection("server=localhost;uid=sa;pwd=;database=myDB"); sc.Open(); SqlDataAdapter sda = new SqlDataAdapter("select * from myTable",sc); DataSet ds = new DataSet(); ad.Fill(ds,"tableFile"); DataTable dt = ds.Tables["tableFile"]; DataRow dr = dt.NewRow(); dr["object"] = ms.GetBuffer(); //object is binary or varbinary // and GetBuffer() returns a byte[] dt.Rows.Add(dbRow); SqlCommandBuilder scb = new SqlCommandBuilder(sda); sda.Update(ds, "tableFile");The instruction: dr["object"] = ms.GetBuffer(); throws the message exception "String or binary data would be truncated" could you help me, please? thanks in advance. -
hello forum, i want to store c# objects in a MS SQL database. I think the best way is to serialize the object to a byte array but i have problems to insert a c# byte array into the database... First, the equivalent type of datum in MS SQL is the binary or varbinary type, isn´t it? well, my code:
MyObject mo = new MyObject(); MemoryStream ms= new MemoryStream(); BinaryFormatter bf = new BinaryFormatter(); b.Serialize(ms,mo); SqlConnection sc = new SqlConnection("server=localhost;uid=sa;pwd=;database=myDB"); sc.Open(); SqlDataAdapter sda = new SqlDataAdapter("select * from myTable",sc); DataSet ds = new DataSet(); ad.Fill(ds,"tableFile"); DataTable dt = ds.Tables["tableFile"]; DataRow dr = dt.NewRow(); dr["object"] = ms.GetBuffer(); //object is binary or varbinary // and GetBuffer() returns a byte[] dt.Rows.Add(dbRow); SqlCommandBuilder scb = new SqlCommandBuilder(sda); sda.Update(ds, "tableFile");The instruction: dr["object"] = ms.GetBuffer(); throws the message exception "String or binary data would be truncated" could you help me, please? thanks in advance.If the error says the string will be truncated, the size of the field you want to store it in, is to small.
-
If the error says the string will be truncated, the size of the field you want to store it in, is to small.
-
ok. thank you. that was the problem! but ... another question: if the maxium size of a varbinary is 8000, how could i store an object with a greater lenght (for example, a file)? thank you. bye.
By using "blob" as the field's type: http://www.winnetmag.com/SQLServer/Article/ArticleID/20461/SQLServer\_20461.html
-
ok. thank you. that was the problem! but ... another question: if the maxium size of a varbinary is 8000, how could i store an object with a greater lenght (for example, a file)? thank you. bye.
By using "blob" as the field's type: http://www.winnetmag.com/SQLServer/Article/ArticleID/20461/SQLServer\_20461.html Even better, just save a reference to the file i.e. save it's path as a string in the database. If you have a file server to store your files on, just have your frontend app copy the images there and then save the path (//FILESERVER/SQLSERVERFILES/file1.jpg) in the database. Although SQL server has better performance serving up images (streamed), if a lot of people start using the app at once, the server might get stuck at just streaming files all the time. By just saving the reference, you place the task of getting the image at the client.