连接数据库大全

 

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();       
}

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

http://download.microsoft.com/download/8/B/D/8BDABAE2-B6EA-41D4-B903-7916EF3690EF/sqljdbc_1.2.2323.101_enu.exe

将安装包解压，会有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

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.*;

publicclass 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";

而sql server 2005 中加载驱动和url的语句则为

String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
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();