数据库链接字符串汇总

链接字符串：
https://www.connectionstrings.com/

通用示例

string ConnStr_EOLDDATA = "$System.Data.SqlClient$Data Source=.;Initial Catalog=EOLDATA;Persist Security Info=True;User ID=sa;Password=321";
string SqlLoginTable = string.Format("select * from [登录表] where [VIN] = '{0}'", textBoxVIN.Text);
DbDataAdapter AdapLogin = BConnString.CreateDataAdapter(SqlLoginTable, ConnStr_EOLDDATA);
DataTable dtLogin = new DataTable();
AdapLogin.Fill(dtLogin);
DataRow drLogin = null;
if (dtLogin.Rows.Count < 1)
    drLogin = dtLogin.Rows.Add();
else drLogin = dtLogin.Rows[0];
//drLogin = dtLogin.Rows.Count < 1 ? dtLogin.Rows.Add() : dtLogin.Rows[0];

Access

链接字符串：$System.Data.OleDb$Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LoginSet.mdb
SQL字符串：select * from [Table] where ZTS = 'zts'

string strSql = string.Format("select * from FinalData where DateTime>#{0}#", dTime);

Postgresql

链接字符串：$Npgsql$Database=LocalDB;Host=LocalHost;Port=5432;Username=postgres;Password=123
SQL字符串：select * from [Table] where VIN='vin' order by ID desc
string strLocalSql = string.Format("select * from [四轮表] order by [测试时间] desc limit 1");

判断字段中包含某字符串所在位

select * from [车型库] where POSITION('{0}' in [VIN])>0
eg:postion('abc' in '123abc123')—>4

时间判断

SELECT * FROM [FAS表] where [VIN]='{0}' and to_char([测试时间],'yyyy-MM-dd HH24:MI:ss')='{1}' limit 1

SQLServer

SQLServer字符串：$System.Data.SqlClient$Data Source=EOLSERVICE;Initial Catalog=EOLData;Persist Security Info=True;User ID=sa;Password=321
SQL字符串：select * from [Table] where VIN='vin' order by ID desc

SQLServer判断字段中包含某字符串所在位

select * from [车型库] where charindex('{0}',[VIN])>0

选择左侧为某字符串

select * from [车型库] where left([VIN],3)='LZW'

Oracle

不需要借助客户端可直接访问数据库
string Ora_ConnStr = "User ID=jcxuser;Password=jcxuser;Data Source =(DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.89)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = yadata)))";

需要安装Oracle客户端并配置链接方可访问
string Ora_ConnStrOLEDB = "Provider=OraOLEDB.Oracle;Data Source=yadata;Persist Security Info=True;Password=jcxuser;User ID=jcxuser";
有时间对比这两种链接字符串的区别，下面可访问但访问数据不全
string Ora_ConnStr = "$System.Data.OleDb$Provider=MSDAORA;Data Source=yadata;Persist Security Info=True;Password=jcxuser;User ID=jcxuser";

OracleConnection conn = new OracleConnection(Ora_ConnStr);
conn.Open();
string OracleSqlStr = string.Format("select * from {0} where SN =  '{1}'", Ora_tableForVSN, sVIN);
DataTable dtVSN = new DataTable();
OracleDataAdapter odaVSN = new OracleDataAdapter(OracleSqlStr,  Ora_ConnStr);
odaVSN.Fill(dtVSN);
conn.Close();

OleDbConnection conn = new OleDbConnection(Ora_ConnStrOLEDB);
conn.Open();
OleDbCommand cmdVSN = conn.CreateCommand();
cmdVSN.CommandText = string.Format("select * from {0} where SN = '{1}'", Ora_tableForVSN, sVIN);
DataTable dtVSN = new DataTable();
OleDbDataAdapter odaVSN = new OleDbDataAdapter();
odaVSN.SelectCommand = cmdVSN;
odaVSN.Fill(dtVSN);
conn.Close();

SQLServer向Oracle数据库传数据

using Oracle.ManagedDataAccess;

OracleConnection cnn = new OracleConnection(mesConnStr);
cnn.Open();
DataTable dtMES = new DataTable();
OracleDataAdapter oda = new OracleDataAdapter(oraSql, mesConnStr);
OracleCommandBuilder ocb = new OracleCommandBuilder(oda);
oda.SelectCommand.CommandText = oraSql;
oda.InsertCommand = ocb.GetInsertCommand();
oda.Fill(dtMES);
cnn.Close();

DataRow drMES = null;
if (dtMES.Rows.Count > 0)
    drMES = dtMES.Rows[0];
else drMES = dtMES.Rows.Add();
foreach (DataColumn dcEOL in upload.Columns)
{
    if (dtMES.Columns.Contains(dcEOL.ColumnName))
        drMES[dcEOL.ColumnName] = drEOL[dcEOL.ColumnName];
}
oda.Update(dtMES);
dtMES.AcceptChanges();

SQLite

//创建数据库表
static void CreateTable()
{
    SQLiteConnection cn = new SQLiteConnection(connStr);//建立数据库连接
    if (cn.State!= System.Data.ConnectionState.Open)
    {
        cn.Open();//打开数据库
        SQLiteCommand cmd = new SQLiteCommand();
        cmd.Connection = cn;//把 SQLiteCommand的 Connection和SQLiteConnection 联系起来
        cmd.CommandText = "CREATE TABLE IF NOT EXISTS t1(id varchar(4),score int)";//输入SQL语句
        cmd.ExecuteNonQuery();//调用此方法运行
    }
    cn.Close();
}

if(!File.Exists(dbPath))
    SQLiteConnection.CreateFile(dbPath);
SQLiteConnection sqliteConn = new SQLiteConnection("data source=" + dbPath);
 if (sqliteConn.State != System.Data.ConnectionState.Open)
 {
     sqliteConn.Open();
     SQLiteCommand cmd = new SQLiteCommand();
     cmd.Connection = sqliteConn;
     //cmd.CommandText = "DROP TABLE IF EXISTS t1";
     //cmd.CommandText = "CREATE TABLE " + "FirstTabel" + "(Name varchar,Team varchar, Number varchar)";
     //cmd.CommandText = "INSERT INTO FirstTabel VALUES('Kobe','Lakers','24')";
     //cmd.CommandText = "REPLACE INTO t1(id,age) VALUES(@id,@age)";
     //cmd.CommandText = "UPDATE t1 SET id=@id,age=@age WHERE id='0123456789'";
     //cmd.CommandText = "DELETE FROM t1 WHERE id='99999'";
     //cmd.CommandText = "ALTER TABLE t1 ADD COLUMN age int";
     cmd.CommandText = "insert into FirstTabel(Name,Team,Number) values(@name,@team,@number)";
     cmd.Parameters.AddWithValue("@name", "Kobe");
     cmd.Parameters.AddWithValue("@Team", "Lakers");
     cmd.Parameters.AddWithValue("@Number", "24");
     cmd.ExecuteNonQuery();
 }
 sqliteConn.Close();
//常用
SQLiteConnection cn = new SQLiteConnection(connStr);
cn.Open();
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText= string.Format("select * from {0} where SN = '{1}'", Ora_tableForVSN, sVIN);
//方法一：用DataAdapter和DataTable类，调用方法为using System.Data
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);
cn.Close();
//方法二：用DataReader，这个效率高些
SQLiteDataReader reader = cmd.ExecuteReader();

Mysql

using MySql.Data.MySqlClient;

MySqlConnection myconn = new MySqlConnection("server=10.102.62.26;database=cartest_reporter;user id=root;password=root;Charset=utf8");
myconn.Open();
MySqlCommand mycom = myconn.CreateCommand();
mycom.CommandText = "SELECT *FROM student1";
MySqlDataAdapter adap = new MySqlDataAdapter(mycom);
MySqlCommandBuilder mysqlcb = new MySqlCommandBuilder(adap);// insert
DataTable dt = new DataTable();
adap.Fill(dt);

/*string MES_ConnStr = @"server = 10.23.1.67;Uid = testams;Pwd = test@ams;database= test;CharSet=utf8";
MySqlConnection conn = new MySqlConnection(MES_ConnStr);
if (conn.State != ConnectionState.Open)
    conn.Open();
strOra_Sql = string.Format("select * from {0}", MESTableName);
MySqlDataAdapter sdap = new MySqlDataAdapter();
MySqlCommand mycmdFind = new MySqlCommand(strOra_Sql, conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(sdap);
DataTable dt = new DataTable();
sdap.SelectCommand = mycmdFind;
sdap.Fill(dt);
DataRow dr = dt.NewRow();

foreach (string s in ListMap.Keys)
{
    dr[s] = drEOL[ListMap[s]];
}
dt.Rows.Add(dr);
sdap.Update(dt);
conn.Close();*/

Excel

DataSet ds = new DataSet();
OleDbConnection conn = new OleDbConnection(strConn);
string sql = string.Format("select * from [{0}$] where [VIN] like '%{1}%'",
    sourceTableName, VINString);
OleDbDataAdapter oada = new OleDbDataAdapter(sql, conn);
oada.Fill(ds);
foreach (DataRow drr in ds.Tables[0].Rows)