在.Net中,存在着多种与数据库交互的方式!SqlConnection与OledbConnection就是其中的两种!
其区别就在于:
OleDB控件用的是OleDb的驱动程序,可以访问各种数据库
SQL控件用的是专用的驱动程序,能高效的访问SQL Server数据库SQLConnection只能访问SQL Server,而OleDbConnection则可以访问所有数据库。
如果只是访问SQL Server的话,SQL比OleDb更快。
建立Connection对象的数据源连接字符串:
"Provider=Microsoft.Jet.Oledb.4.0;Data Source=Excel 文件物理路径 + ";Extended Properties=Excel 8.0";
DataAdapter对象中的SQL语句应为:"Select 字段列表 From [工作表名$]"
具体示例:
<%@ Import NameSpace="System.Data"%>
<%@ Import NameSpace="System.Data.OleDb"%>
<script Language="C#" runat="server">
void Page_Load(Object sender,EventArgs e)
{
string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Server.MapPath("Book1.xls") + ";Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter adp = new OleDbDataAdapter("Select * from [Sheet1$]",conn);
DataSet ds = new DataSet();
adp.Fill(ds,"Book1");
ExlDataGrid.DataSource = ds.Tables["Book1"].DefaultView;
ExlDataGrid.DataBind();
}
</script>
<html>
<body>
<h4 align="center">读取Excel文件</h4>
<asp:DataGrid id="ExlDataGrid" width="100%" HeaderStyle-BackColor="#999999" ForeColor="#333333" runat="server"></asp:DataGrid>
</body>
</html>
是button按钮一点就开始导入,我导的是excel是第二三colum..如下:
private void Button1_Click(object sender, System.EventArgs e)
{
string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'D:/ExportToExcel/excel/test.xls';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection (mystring);
OleDbDataAdapter myDa =new OleDbDataAdapter("select * from [Sheet1$]",cnnxls);
DataSet myDs =new DataSet();
myDa.Fill(myDs);
if(myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
string CnnString="Provider=SQLOLEDB;database=testnews;server=(local);uid=sa;pwd=";
OleDbConnection conn =new OleDbConnection(CnnString);
conn.Open ();
OleDbCommand myCmd =null;
for(int i=0; i<myDs.Tables[0].Rows.Count; i++)
{
strSql="insert into news(title,body) values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "', '";
strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "')";
try
{
myCmd=new OleDbCommand(strSql,conn);
myCmd.ExecuteNonQuery();
Label8.Text = "<script language=javascript>alert('数据导入成功.');</script>";
}
catch
{
Label8.Text = "<script language=javascript>alert('数据导入失败.');</script>";
}
}
conn.Close();
}
}
}
<span style="word-wrap: normal; word-break: normal; color: rgb(0, 0, 255);"><span style="font-family:Courier New;word-wrap: normal; word-break: normal;">"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"</span></span>
<span style="word-wrap: normal; word-break: normal; color: rgb(0, 0, 255);"><span style="font-family:Courier New;word-wrap: normal; word-break: normal;"><span style="color:#000000;word-wrap: normal; word-break: normal;">针对如果上连接字符串 对相关属性进行说明如下:"<span style="word-wrap: normal; word-break: normal;">HDR=Yes;”指示第一行中包含列名,而不是数据,"IMEX=1;”通知驱动程序始终将</span></span></span></span><span style="word-wrap: normal; word-break: normal; color: rgb(0, 0, 255);"><span style="word-wrap: normal; word-break: normal; color: rgb(0, 0, 0);"><span style="font-family:Courier New;word-wrap: normal; word-break: normal;">“互混”数据列作为文本读取。Excel 8.0 针对Excel2000及以上版本,Excel5.0 针对Excel97。</span></span></span>
</pre><pre name="code" style="white-space: normal; color: rgb(70, 70, 70); font-size: 14px; line-height: 21px; background-color: rgb(211, 235, 239);"><span style="word-wrap: normal; word-break: normal; color: rgb(0, 0, 255);">using System.Data.OleDb; using System.Data; String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:/test.xls;" + "Extended Properties=Excel 8.0;"; OleDbConnection objConn = new OleDbConnection(sConnectionString); objConn.Open(); OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [sheet1]", objConn); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); objAdapter1.SelectCommand = objCmdSelect; DataSet objDataset1 = new DataSet(); //将Excel中数据填充到数据集 objAdapter1.Fill(objDataset1, "XLData"); objConn.Close();</span>
从上面可以看出,使用ADO.NET可将Excel当作普通数据库,使用SQL语句来操作。
通过ADO.NET获取Excel文件的各Sheet名称,可使用元数据方式:
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:/test.xls;" + "Extended Properties=Excel 8.0;"; OleDbConnection cn = new OleDbConnection(sConnectionString); cn.Open(); DataTable tb = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow row in tb.Rows) { //遍历弹出各Sheet的名称 MessageBox.Show(row["TABLE_NAME"]); }
关于使用ADO.NET创建并写入Excel文件与普通数据库操作极为类似,参见以下代码:
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:/test.xls;" + "Extended Properties=Excel 8.0;"; OleDbConnection cn = new OleDbConnection(sConnectionString); string sqlCreate = "CREATE TABLE TestSheet ([ID] INTEGER,[Username] VarChar,[UserPwd] VarChar)"; OleDbCommand cmd = new OleDbCommand(sqlCreate, cn); //创建Excel文件:C:/test.xls cn.Open(); //创建TestSheet工作表 cmd.ExecuteNonQuery(); //添加数据 cmd.CommandText = "INSERT INTO TestSheet VALUES(1,'elmer','password')"; cmd.ExecuteNonQuery(); //关闭连接 cn.Close();