Question

c#连接SQL数据库的使用方法

最好进行一下文字说明！连接别的数据库的方法也说一下吧！

Answer 1

1.提取单条记录
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
SqlCommand cmd = new SqlCommand("Select Count(*) From jobs",cn);
cn.Open();
%%2=cmd.ExecuteScalar(); //Message.InnerHtml
}

2.单值比较
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
SqlCommand cmd = new SqlCommand("Select Count(*) From jobs",cn);
cn.Open();
%%2=cmd.ExecuteScalar(); //Message.InnerHtml
if(%%2==%%3)
{
%%4
}
}

3.显示表格
//using System.Data;
//using System.Data.SqlClient;
DataSet ds=null;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
cn.Open();
cmd=new SqlDataAdapter("Select * From Author",cn);
ds=new DataSet();
cmd.Fill(ds,%%2); //"作者"
MyDataGrid.DataSource=ds.Tables(%%2).DefaultView; //"作者"
MyDataGrid.DataBind();
}

4.操作表格
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
cn.Open();
cmd=new SqlDataAdapter("Select * From Author",cn);
ds=new DataSet();
cmd.Fill(ds,%%2); //"作者"
MyDataGrid.DataSource=ds.Tables(%%2).DefaultView; //"作者"
MyDataGrid.DataBind();
}

5.数值范围查询
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
}

6.关闭时断开连接
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
}

7.执行命令
//using System.Data;
//using System.Data.SqlClient;
using (SqlConnection cn = new SqlConnection(%%1))
//ConfigurationManager.ConnectionStrings["db2ConnectionString"].ConnectionString
//Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
{
SqlCommand cmd = new SqlCommand("insert userRegister_t values('"
+ TextBox1.Text + "','" + TextBox2.Text + "')",cn);
cn.Open();
cmd.ExecuteNonQuery();
}

7.Oracle8/8i/9i数据库（thin模式）
//using System.Data;
Oracle Set Your custom connection strings values

ODBC
New version

Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;

Old version

Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;

OLE DB, OleDbConnection (.NET)
Standard security
This connection string uses a provider from Microsoft.
Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

Standard Security
This connection string uses a provider from Oracle.
Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

Trusted Connection

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;

OracleConnection (.NET)
Standard

Data Source=MyOracleDB;Integrated Security=yes;

This one works only with Oracle 8i release 3 or later

Specifying username and password

Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no;

This one works only with Oracle 8i release 3 or later
Missing the System.Data.OracleClient namespace? Download .NET Managed Provider for Oracle >>
Great article! "Features of Oracle Data Provider for .NET" by Rama Mohan G. at C# Corner >>

Omiting tnsnames.ora
This is another type of Oracle connection string that doesn't rely on you to have a DSN for the connection. You create a connection string based on the format used in the tnsnames.ora file without the need to actually have one of these files on the client pc.
SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword;

Core Labs OraDirect (.NET)
Standard

User ID=myUsername;Password=myPassword;Host=ora;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;

Read more at Core Lab >>
And at the product page >>

Data Shape
MS Data Shape

Provider=MSDataShape.1;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;User Id=myUsername;Password=myPassword;

16.程序计时
DateTime runTime = Convert.ToDateTime(System.Configuration.ConfigurationSettings.AppSettings["TimerRunAt"]);
//运行时间
TimeSpan runTime = new TimeSpan(runTime.Ticks);
//现在时间
TimeSpan timeNow = new TimeSpan(DateTime.Now.Ticks);
//时间间隔
TimeSpan ts = runTime.Subtract(timeNow);
//如果运行时间与当前时间的差大于0，则任务执行时间为当日
if(ts.TotalMilliseconds > 0)
{
return ts.TotalMilliseconds;
}
//如果运行时间与当前时间的差小于0，则任务执行时间为次日
Else
{
//差值的绝对值应加1天
return ts.Duration().Add(new TimeSpan(1,0,0,0,0)).TotalMilliseconds;
}

17.延时
//using System.Threading;
Thread.Sleep(Int32.Parse(%%1));

18.连接Excel文件
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excel文件名（绝对路径） +";Extended Properties=Excel 8.0;";

Answer 2

不知道楼主有没有 学到 接口。
其实，只要在 一个类中，创建了连接对象，那么之后的 类要连接数据库大可以来调用这个类。并且利用接口来编程。
只要就该配置文件的 一两个单词， 程序就自动实现 对不同数据库的链接。
现在这里有一个 DBHelper 类，和一个需要链接数据库的类。楼主看看。

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;

namespace DAL_Dream_Girl
{

using DATA_All_Static;
using MODELS_Dream_Girl;

public class DB_Helper
{

public static IDbConnection Connection = null;
public static IDbCommand Command = null;

public bool Load_Server_DB()
{
if (All_Load_String.load_DB_Server == "Sql_Server")//如果读取配置文件时，字符串是 “Sql_Server”.All_Load_String是我自己定义的一个静态类，不是系统的，他只是得到一个字符串。
{
Connection = new SqlConnection(All_Load_String.sql_load);
Command = new SqlCommand();
Command.Connection = Connection;
}
else if (All_Load_String.load_DB_Server == "Access_Server")//如果读取配置文件时，字符串是 “Access_Server”
{
Connection = new OleDbConnection(All_Load_String.access_load);
Command = new OleDbCommand();
Command.Connection = Connection;
}

bool fanHui = try_Load_DB();

return fanHui;

}

}
}

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;

namespace DAL_Dream_Girl
{

public class Pan_Main_dal
{
private static IDbConnection connection = null;
private static IDbCommand command = null;
public Pan_Main_dal()
{
connection = DB_Helper.Connection; //调用 DB_Helper 的链接对象
command = DB_Helper.Command;
command.Connection = connection;
}

private int fanHui = -1;

//查询数据
public int Select_Will_Go_dal()
{
fanHui = 0;
string sql = "select * from biao2";
command.CommandText = sql;

Select_Main_All_News();
return fanHui;
}

private void Select_Main_All_News()
{
try
{
connection.Open();
IDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string GoListTime = Convert.ToString(reader["GoTime"]);
string GoListTopic = Convert.ToString(reader["GoTopic"]);

.......
fanHui = fanHui + 1;
}
reader.Close();
}
catch { fanHui = -1; }
finally { connection.Close(); }
}

}
}

在第二个类中， IDbConnection IDbCommand IDataReader 全部都是接口。所有的所作所为都是在用接口做的。
也就是说，即使数据库不同，也一样的可以链接的十分健康，而不会出现异常。

还有一点，楼主现在也已经是2级了，悬赏积分为 0 毕竟会让人不好想滴——当然，积分虽然并不重要，但让人的感觉就是问题本身并不重要。等以后楼主等级提高了，希望提问就不要再 以 0 为悬赏金额了。

Answer 3

<%
Const SqlDatabaseName = "test" ' 数据库名字'
Const SqlPassword = "123" '数据库密码'
Const SqlUsername = "sa" '数据库帐户'
Const SqlLocalName = "127.0.0.1" '数据库地址'

'数据库连接函数'
Sub ConnectionDatabase
Dim ConnStr
ConnStr = "Provider = Sqloledb; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source = " & SqlLocalName & ";"
On Error Resume Next
Set conn = Server.CreateObject("ADODB.Connection")
conn.open ConnStr
If Err Then
err.Clear
Set Conn = Nothing
Response.Write "数据库连接出错，请检查连接字串!"
Response.End
End If
End Sub

'默认打开数据库连接
call ConnectionDatabase()
%>

Answer 4

<connectionStrings>
<add name="xxx" connectionString="server=.;database=Radish;uid=sa;password="/>
</connectionStrings>
string connString = ConfigurationManager.ConnectionStrings["xxx"].ConnectionString;
注意：如果在类库中使用ConfigurationManager，则需要添加对System.Configuration的引用，不光是在命名空间中using，还要在类库项目中右键添加对System.Configuration的引用。

ASP.NET 1.1中的写法：

WebConfig:

<appSettings>
<add key="HRConnectionString" value="Data Source=.;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password="/>
</appSettings>

读取方法：

string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];