如何用ADO.NET 把附件 保存在SQL server里面 我们的销售订单需要插入一个附件, 让所有的用户都可以取到, 附件可以是任何格式,我想只能是利用ADO.NET,保存在SQL SERVER 里面好啦, 至于这方面的提示,高手给一些吧. 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 参考:using System;using System.Data;using System.Data.SqlClient;using System.IO;public class EmployeeData{ public static void Main() { DateTime hireDate = DateTime.Parse("4/27/98"); int newID = AddEmployee("Smith", "John", "Sales Representative", hireDate, 5, "smith.bmp"); Console.WriteLine("New Employee added. EmployeeID = " + newID); } public static int AddEmployee(string lastName, string firstName, string title, DateTime hireDate, int reportsTo, string photoFilePath) { using (SqlConnection connection = new SqlConnection( "Data Source=(local);Integrated Security=true;Initial Catalog=Northwind;")) { SqlCommand addEmp = new SqlCommand( "INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " + "Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" + "SELECT @Identity = SCOPE_IDENTITY();" + "SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity", connection); addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = lastName; addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName; addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = title; addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hireDate; addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value = reportsTo; SqlParameter idParm = addEmp.Parameters.Add("@Identity", SqlDbType.Int); idParm.Direction = ParameterDirection.Output; SqlParameter ptrParm = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16); ptrParm.Direction = ParameterDirection.Output; connection.Open(); addEmp.ExecuteNonQuery(); int newEmpID = (int)idParm.Value; StorePhoto(photoFilePath, (byte[])ptrParm.Value, connection); return newEmpID; } } public static void StorePhoto(string fileName, byte[] pointer, SqlConnection connection) { // The size of the "chunks" of the image. int bufferLen = 128; SqlCommand appendToPhoto = new SqlCommand( "UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes", connection); SqlParameter ptrParm = appendToPhoto.Parameters.Add( "@Pointer", SqlDbType.Binary, 16); ptrParm.Value = pointer; SqlParameter photoParm = appendToPhoto.Parameters.Add( "@Bytes", SqlDbType.Image, bufferLen); SqlParameter offsetParm = appendToPhoto.Parameters.Add( "@Offset", SqlDbType.Int); offsetParm.Value = 0; // Read the image in and write it to the database 128 (bufferLen) bytes at a time. // Tune bufferLen for best performance. Larger values write faster, but // use more system resources. FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); BinaryReader br = new BinaryReader(fs); byte[] buffer = br.ReadBytes(bufferLen); int offset_ctr = 0; while (buffer.Length > 0) { photoParm.Value = buffer; appendToPhoto.ExecuteNonQuery(); offset_ctr += bufferLen; offsetParm.Value = offset_ctr; buffer = br.ReadBytes(bufferLen); } br.Close(); fs.Close(); }} 在WebSite下,创建虚拟目录,指向这个文件夹,给予aspnet读写权限。 我也希望如此,问题是我的是WINFORM的,不是WEB的, 其中你只看 StorePhoto 这个方法就行了,它将任意二进制数据保存在数据中。另外,我还不知道怎样修改自己发过的帖子?似乎没有权限? 另外呢,我想SQL SERVER 用什么语句把二进制写进去,难道是用insert into 的方法吗 你自己都说了永ADO。Net,对于ado.net来说,字段是什么类型根本不重要给他赋值就可以了,附件就是text数据而已,对于c#就是byte[],不用普通的sql来处理,insert into也不行,需要用sql特殊的函数才可以采用ado.net存储过程方式就可以了。http://topic.csdn.net/t/20030815/14/2150499.html byte[] 是整数吧, 怎么用在这里,是不是用整数数组可以代替附件 参考一下别人的吧http://maxianghui.cnblogs.com/archive/2006/07/12/448943.html他的例子是保存和提取图片文件,和你的同理string filename = this.openFileDialog1.FileName;139 SqlConnection conn = new SqlConnection("server=192.168.2.200;integrated security = sspi;database = northwind");140 SqlCommand cmd = new SqlCommand("insert imgtable values(@imgname,@imgData)",conn);141 SqlParameter pm = new SqlParameter("@imgname",SqlDbType.VarChar,100);142 pm.Value = filename;143 SqlParameter pm1 = new SqlParameter("@imgData",SqlDbType.Image);144 FileStream fs = new FileStream(filename,FileMode.Open);145 int len = (int)fs.Length;146 byte[] fileData = new byte[len];147 fs.Read(fileData,0,len);148 fs.Close();149150 pm1.Value = fileData;151 cmd.Parameters.Add(pm);152 cmd.Parameters.Add(pm1); 听说修改 image 类型的时候,不能用update SQL语句,只能用updatetext语句,是这样子吗 Reflection.MethodInfo如何反射一个静态方法来调用 C#类库中有没有交换两个变量的函数 【求助】DbCommandBuilder 在单表维护中的使用详解? 请问怎样用C#生成二进制文件?(就是把string型以二进制的形式保存,用TXT打开看到的是乱码) 求C#操作Oracle二进制大对象的类 2005年下半年 软件设计师 下午试卷--试题一的问题3 急!!!急!!!关于数据库添加列问题。 求一套类似开心网好友导入功能(现金购买) 我做的是写论文网站,如何写论文。 序列化的问题 C#读入XML数据到数据库 将Datagirdview关联到DataView
using System.Data;
using System.Data.SqlClient;
using System.IO;public class EmployeeData
{
public static void Main()
{
DateTime hireDate = DateTime.Parse("4/27/98");
int newID = AddEmployee("Smith", "John", "Sales Representative",
hireDate, 5, "smith.bmp");
Console.WriteLine("New Employee added. EmployeeID = " + newID);
} public static int AddEmployee(string lastName, string firstName,
string title, DateTime hireDate, int reportsTo, string photoFilePath)
{
using (SqlConnection connection = new SqlConnection(
"Data Source=(local);Integrated Security=true;Initial Catalog=Northwind;"))
{ SqlCommand addEmp = new SqlCommand(
"INSERT INTO Employees (LastName, FirstName, Title, HireDate, ReportsTo, Photo) " +
"Values(@LastName, @FirstName, @Title, @HireDate, @ReportsTo, 0x0);" +
"SELECT @Identity = SCOPE_IDENTITY();" +
"SELECT @Pointer = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = @Identity",
connection); addEmp.Parameters.Add("@LastName", SqlDbType.NVarChar, 20).Value = lastName;
addEmp.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10).Value = firstName;
addEmp.Parameters.Add("@Title", SqlDbType.NVarChar, 30).Value = title;
addEmp.Parameters.Add("@HireDate", SqlDbType.DateTime).Value = hireDate;
addEmp.Parameters.Add("@ReportsTo", SqlDbType.Int).Value = reportsTo; SqlParameter idParm = addEmp.Parameters.Add("@Identity", SqlDbType.Int);
idParm.Direction = ParameterDirection.Output;
SqlParameter ptrParm = addEmp.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
ptrParm.Direction = ParameterDirection.Output; connection.Open(); addEmp.ExecuteNonQuery(); int newEmpID = (int)idParm.Value; StorePhoto(photoFilePath, (byte[])ptrParm.Value, connection); return newEmpID;
}
} public static void StorePhoto(string fileName, byte[] pointer,
SqlConnection connection)
{
// The size of the "chunks" of the image.
int bufferLen = 128; SqlCommand appendToPhoto = new SqlCommand(
"UPDATETEXT Employees.Photo @Pointer @Offset 0 @Bytes",
connection); SqlParameter ptrParm = appendToPhoto.Parameters.Add(
"@Pointer", SqlDbType.Binary, 16);
ptrParm.Value = pointer;
SqlParameter photoParm = appendToPhoto.Parameters.Add(
"@Bytes", SqlDbType.Image, bufferLen);
SqlParameter offsetParm = appendToPhoto.Parameters.Add(
"@Offset", SqlDbType.Int);
offsetParm.Value = 0; // Read the image in and write it to the database 128 (bufferLen) bytes at a time.
// Tune bufferLen for best performance. Larger values write faster, but
// use more system resources.
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs); byte[] buffer = br.ReadBytes(bufferLen);
int offset_ctr = 0; while (buffer.Length > 0)
{
photoParm.Value = buffer;
appendToPhoto.ExecuteNonQuery();
offset_ctr += bufferLen;
offsetParm.Value = offset_ctr;
buffer = br.ReadBytes(bufferLen);
} br.Close();
fs.Close();
}
}
string filename = this.openFileDialog1.FileName;
139 SqlConnection conn = new SqlConnection("server=192.168.2.200;integrated security = sspi;database = northwind");
140 SqlCommand cmd = new SqlCommand("insert imgtable values(@imgname,@imgData)",conn);
141 SqlParameter pm = new SqlParameter("@imgname",SqlDbType.VarChar,100);
142 pm.Value = filename;
143 SqlParameter pm1 = new SqlParameter("@imgData",SqlDbType.Image);
144 FileStream fs = new FileStream(filename,FileMode.Open);
145 int len = (int)fs.Length;
146 byte[] fileData = new byte[len];
147 fs.Read(fileData,0,len);
148 fs.Close();
149
150 pm1.Value = fileData;
151 cmd.Parameters.Add(pm);
152 cmd.Parameters.Add(pm1);