1
.csv;.txt;.tab;
ODBC
Standard:
'Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:/txtFilesFolder/;Extensions=asc,csv,tab,txt;'
OLE DB
Standard:
‘Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/txtFilesFolder/;Extended Properties=’’text;HDR=Yes;FMT=Delimited’’’
‘HDR=Yes;’ indicates that the first row contains columnnames, not data
1.1 Vb.net
Dim strConn As String
Dim strSql As String = "select * from" + " 1.csv"
Dim objConn As OdbcConnection
Dim dataSet As DataSet = New DataSet
Dim dataRow As DataRow
Dim odbcCSVDataAdapter As New OdbcDataAdapter
dataSet.Tables.Add(New DataTable())
strConn = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="
strConn += "e:/"
strConn += ";Extensions=asc,csv,tab,txt;"
objConn = New OdbcConnection(strConn)
odbcCSVDataAdapter = New OdbcDataAdapter(strSql, objConn)
odbcCSVDataAdapter.Fill(dataSet.Tables(0))
dataSet.Tables(0).Columns(0).ColumnName = "
商品コード"
For Each dataRow In dataSet.Tables(0).Rows
If dataRow.Item("
商品コード"
).ToString() = "21" Then
MsgBox(dataSet.Tables(0).Columns(1).ColumnName)
End If
Next
Me.TextBox1.Text = dataSet.Tables(0).Rows(2).Item("
商品コード"
).ToString()
1.2 Asp.net
void Page_Load(Object sender, EventArgs e)
{
string ConnectionString;
string SQLString;
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(".") + ";Extended Properties=/"Text;HDR=yes;FMT=Delimited/"";
SQLString = "Select * from ReportFile.txt";
System.Data.OleDb.OleDbConnection ConnectionText = new System.Data.OleDb.OleDbConnection();
ConnectionText.ConnectionString = ConnectionString;
ConnectionText.Open();
System.Data.OleDb.OleDbDataAdapter AdapterText = new System.Data.OleDb.OleDbDataAdapter(SQLString, ConnectionText);
System.Data.DataSet DataSetText = new System.Data.DataSet("TextFiles");
AdapterText.Fill(DataSetText, "TextFile");
DataGrid1.DataSource = DataSetText;
DataGrid1.DataBind();
ConnectionText.Close();
}
{
string ConnectionString;
string SQLString;
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(".") + ";Extended Properties=/"Text;HDR=yes;FMT=Delimited/"";
SQLString = "Select * from ReportFile.txt";
System.Data.OleDb.OleDbConnection ConnectionText = new System.Data.OleDb.OleDbConnection();
ConnectionText.ConnectionString = ConnectionString;
ConnectionText.Open();
System.Data.OleDb.OleDbDataAdapter AdapterText = new System.Data.OleDb.OleDbDataAdapter(SQLString, ConnectionText);
System.Data.DataSet DataSetText = new System.Data.DataSet("TextFiles");
AdapterText.Fill(DataSetText, "TextFile");
DataGrid1.DataSource = DataSetText;
DataGrid1.DataBind();
ConnectionText.Close();
}
2
读取
excel
2.1
Asp.net
private DataSet CreateDataSource(){
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C://Inetpub//wwwroot//contacts.xls;"+
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [ContactList$]", strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
return myDataSet;
}
public void Page_Load(Object sender, EventArgs e){
if (!IsPostBack) {
mygrid.DataSource = CreateDataSource();
mygrid.DataBind();
}
3
SQL sever
3.1 Jsp连接SQLsever 2005
3.1.1 安装好SQLsever 2005,建立数据库。
3.1.2 下载
SQL Server 2005 driver for JDBC
将安装包解压,会有
sqljdbc.jar
文件,其他文件没用。在
环
境
变
量
classpath
后面追加
C:/.../sqljdbc.jar
3.1.3
设
置
SQLEXPRESS
服
务
器
:
a.
打
开
SQL Server Configuration Manager -> SQLEXPRESS
的
协议
-> TCP/IP
b. 右 键单击 启 动 TCP/IP
c. 双 击进 入属性 , 把 IP 地址中的 IP all 中的 TCP 端口 设 置 为 1433
d. 重新启 动 SQL Server 2005 服 务 中的 SQLEXPRESS 服 务 器
e. 关闭 SQL Server Configuration Manager
b. 右 键单击 启 动 TCP/IP
c. 双 击进 入属性 , 把 IP 地址中的 IP all 中的 TCP 端口 设 置 为 1433
d. 重新启 动 SQL Server 2005 服 务 中的 SQLEXPRESS 服 务 器
e. 关闭 SQL Server Configuration Manager
3.1.4
设置工程:
在工程的
Build Path
à
configure Build Path
à
java build path
中的
libaries
标签中点
Add external Jars
将
sqljdbc.jar
添加进去。然后将
sqljdbc.jar
包放入
workspace/
工程名
/WEB-INF/lib
中(否则,工程无法识别驱动)
3.1.5
代码测试
package count;import java.sql.*;public class Jdbc {public Jdbc(){}public String getInfo(){String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver" ;String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName= Working management " ; // 服务器名称String userName = " sa " ; // 数据库名称String userPwd = " sts " ; // 数据库密码Connection dbConn;try {Class.forName(driverName).newInstance(); 获得驱动的对象,并将其实例化。dbConn = DriverManager.getConnection(dbURL, userName, userPwd);System. out .println( "Connection Successful!" );return "ok" ;} catch (Exception e) {e.printStackTrace();return "faliur" ;}}}
注
:
1.
因
为
SQLexpress
服
务
器默
认
是禁用的并且端口号没有配置
,
所以要
进
行重新
设
置
2.
如果你以前用
java
连
接
sql server 2000
的
话
就要注意了
:
在
sql server 2000
中加
载驱动
和
URL
路径的
语
句是
String driverName = "com.microsoft.
jdbc
.
sqlserver
.SQLServerDriver";
String dbURL = " jdbc :microsoft: sqlserver ://localhost:1433; DatabaseName=sample";
String dbURL = " jdbc :microsoft: sqlserver ://localhost:1433; DatabaseName=sample";
而
sql server 2005
中加
载驱动
和
url
的
语
句
则为
String driverName = "com.microsoft.
sqlserver
.
jdbc
.SQLServerDriver";
String dbURL = " jdbc:sqlserver ://localhost:1433; DatabaseName=sample";
String dbURL = " jdbc:sqlserver ://localhost:1433; DatabaseName=sample";
如果写法
错误
将会找不到
驱动
.
取得数据部分:
String sql =
"select employee_no from m_employee"
;
Statement stmt =
dbConn
.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while
(rs.next()) {
return
rs.getString(
"employee_no"
);
}
rs.close();
stmt.close();