先设置web.config <appSettings> <add key="ConnectionString" value="DATA SOURCE=127.0.0.1;UID=sa;PWD=sa;DATABASE=school"></add> </appSettings> 连接类: using System; using System.Data; using System.Configuration; using System.Data.SqlClient; using System.Text; using System.Text.RegularExpressions;namespace school.cs1 { /// <summary> /// adohelper 的摘要说明。 /// </summary> public class adohelper { // 下面的con是私有成员变量 SqlConnection con; // 下面的m_ConnectionString是静态成员变量 static String m_ConnectionString; public adohelper() { con = new SqlConnection(adohelper.ConnectionString()); } public SqlDataReader ExecuteRead(string cmd) { this.CheckConnection(); SqlDataReader dr = null;
try { SqlCommand dc = new SqlCommand(cmd,con); dr = dc.ExecuteReader(CommandBehavior.CloseConnection); } catch(SqlException se) { throw new Exception("Error in SQL", se); } return dr; } // 私有方法,检查数据连接是否打开,没有则打开 private void CheckConnection() { try { if (con.State != ConnectionState.Open) con.Open(); } catch (System.Data.SqlClient.SqlException se) { throw new Exception("Failed to Open connection.", se); } }// 静态方法 static String ConnectionString() { if (m_ConnectionString == null) { m_ConnectionString = (String) ConfigurationSettings.AppSettings["ConnectionString"];
if (m_ConnectionString == null) { throw new Exception("Connect string value not set in Web.config"); } } return m_ConnectionString; }// 私有访问方法,释放资源 private void Dispose() { try { if (con.State == ConnectionState.Open) con.Close(); } catch(Exception se) { throw new Exception("Error in SQL", se); } } } } }
查询SQL语句作为条件调用FillDataSet,可以得到数据集
调用: adohelper myado=new adohelper(); SqlDataReader dr=myado.ExecuteRead("select 基数 from 基数设置 where 时间头<='"+nowstring+"' and 时间尾>'"+nowstring+"' and 类型='"+cond+"'"); if(dr.Read()) { onlines=Convert.ToInt32(dr["基数"])+(int)Application["online"]; }
SqlConnection tConnection=new SqlConnection(strConnection);
SqlCommand tCommand=new SqlCommand();
tCommand.Connection=tConnection;
tCommand.CommandText="select * from a1"//a1是表名
tConnection.Open();
SqlDataReader sdr;
sdr=tCommand.ExecuteNonQuery();
this.DataGrid1.DataSource=sdr;
this.DataGrid1.DataBind();
strConn = "Data Source=local;Initial Catalog=dbname;User Id=sa;Password=aaa
strSQL = "select * from person ";
SqlDataAdapter acqda = new SqlDataAdapter(strSQL,strConn);
DataSet ds = new DataSet();
acqda.Fill(ds,"Cook");
/// 打开数据库连接
/// </summary>
/// <returns>数据库连接</returns>
public static SqlConnection OpenConnection()
{
SqlConnection Conn = new SqlConnection();
Conn.ConnectionString = "Data Source=liuchunjian; Integrated Security=SSPI; Initial Catalog=CottonTextile";
Conn.Open(); return Conn;
}
/// 创建数据集
/// </summary>
/// <param name="strSQL">Sql查询语句</param>
/// <param name="TableName">数据表名</param>
/// <returns>DataSet数据集</returns>
public static DataSet FillDataSet(string strSQL , string TableName)
{
SqlConnection Conn = OpenConnection(); DataSet DS=new DataSet();
SqlDataAdapter dt = new SqlDataAdapter(strSQL,Conn);
dt.Fill(DS , TableName);
dt.Dispose();
Conn.Close(); return DS;
}
<appSettings>
<add key="ConnectionString" value="DATA SOURCE=127.0.0.1;UID=sa;PWD=sa;DATABASE=school"></add>
</appSettings>
连接类:
using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Text;
using System.Text.RegularExpressions;namespace school.cs1
{
/// <summary>
/// adohelper 的摘要说明。
/// </summary>
public class adohelper
{
// 下面的con是私有成员变量
SqlConnection con;
// 下面的m_ConnectionString是静态成员变量
static String m_ConnectionString; public adohelper()
{
con = new SqlConnection(adohelper.ConnectionString());
}
public SqlDataReader ExecuteRead(string cmd)
{
this.CheckConnection(); SqlDataReader dr = null;
try
{
SqlCommand dc = new SqlCommand(cmd,con);
dr = dc.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(SqlException se)
{
throw new Exception("Error in SQL", se);
}
return dr;
}
// 私有方法,检查数据连接是否打开,没有则打开
private void CheckConnection()
{
try
{
if (con.State != ConnectionState.Open)
con.Open();
}
catch (System.Data.SqlClient.SqlException se)
{
throw new Exception("Failed to Open connection.", se);
}
}// 静态方法
static String ConnectionString()
{
if (m_ConnectionString == null)
{
m_ConnectionString = (String) ConfigurationSettings.AppSettings["ConnectionString"];
if (m_ConnectionString == null)
{
throw new Exception("Connect string value not set in Web.config");
}
}
return m_ConnectionString;
}// 私有访问方法,释放资源
private void Dispose()
{
try
{
if (con.State == ConnectionState.Open)
con.Close();
}
catch(Exception se)
{
throw new Exception("Error in SQL", se);
}
}
}
}
}
adohelper myado=new adohelper();
SqlDataReader dr=myado.ExecuteRead("select 基数 from 基数设置 where 时间头<='"+nowstring+"' and 时间尾>'"+nowstring+"' and 类型='"+cond+"'");
if(dr.Read())
{
onlines=Convert.ToInt32(dr["基数"])+(int)Application["online"];
}
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;/// <summary>
/// 欢迎使用'数据服务生',此class提供对SQL Server数据库的支持,以及对数据的操作,作者'烙饼'.
/// 请不要修改此句,Email:[email protected] 烙饼
/// 应用 classname name = new classname(参数表)
/// </summary>
public class DataWaiter
{
private string strConn; //数据库连接字符串
private string serverName; //数据库服务器名称
private string uid; //用户名
private string psw; //密码
private string dataBase; //数据库名称
private char connType; //连接字符穿类型
private string strError;
private SqlConnection conn;
private SqlDataAdapter adapter;
private SqlCommand comm; private event EventHandler Error; //发布事件 /// <summary>
/// 错误事件
/// </summary>
private void DataWaiter_Error(object sender, EventArgs e)
{
MessageBox.Show(this.strError,"Error");
} /// <summary>
/// 使用连接字符串构构造此类,推荐对数据库连接字符串有所了解的朋友使用.
/// </summary>
/// <param name="strConn">SQL Server数据库连接字符串</param>
public DataWaiter(string strConn)
{
this.strConn = strConn;
this.Error +=new EventHandler(DataWaiter_Error);//订阅事件
} /// <summary>
/// 如果您的SQL Server使用Windows集成验证方式,既无须用户名密码,请使用此构造方法构造'数据服务生'.
/// </summary>
/// <param name="serverName">SQL Server主机实例名称</param>
/// <param name="dataBase">数据库名称</param>
public DataWaiter(string serverName,string dataBase)
{
this.serverName = serverName;
this.dataBase = dataBase;
this.connType = 'S'; //此处'S'代表使用Windows登陆信息作为SQL Server登陆信息,SSPI.
this.constructStrConn();
this.Error +=new EventHandler(DataWaiter_Error);//订阅事件
} /// <summary>
/// 如果您的SQL Server使用混合验证方式,而使用的用户密码为空,请使用此构造方法构造'数据服务生'.
/// </summary>
/// <param name="serverName">SQL Server主机实例名称</param>
/// <param name="uid">登陆用户名</param>
/// <param name="dataBase">数据库名称</param>
public DataWaiter(string serverName,string uid,string dataBase)
{
this.serverName = serverName;
this.uid = uid;
this.dataBase = dataBase;
this.connType = 'U'; //无密码的连接
this.constructStrConn();
this.Error +=new EventHandler(DataWaiter_Error);//订阅事件
} /// <summary>
/// 如果您的SQL Server使用混合验证方式,请使用此构造方法构造'数据服务生'.
/// </summary>
/// <param name="serverName">SQL Server主机实例名称</param>
/// <param name="uid">登陆用户名</param>
/// <param name="psw">登陆密码</param>
/// <param name="dataBase">数据库名称</param>
public DataWaiter(string serverName,string uid,string psw,string dataBase)
{
this.serverName = serverName;
this.uid = uid;
this.psw = psw;
this.dataBase = dataBase;
this.connType = 'P'; //有密码的的连接
this.constructStrConn();
this.Error +=new EventHandler(DataWaiter_Error);//订阅事件
}
/// <summary>
/// 对于不同的构造函数,使用不同的方法打造数据库连接字符串.
/// </summary>
private void constructStrConn()
{
switch( this.connType )
{
case 'S':
this.strConn = @"Server=" + this.serverName + ";" +
"Integrated Security=SSPI;" +
"Database=" + this.dataBase + ";";
break;
case 'U':
this.strConn = @"Server=" + this.serverName + ";" +
"user id=" + this.uid + ";" +
"Database=" + this.dataBase + ";";
break;
case 'P':
this.strConn = @"Server=" + this.serverName + ";" +
"user id=" + this.uid + ";" +
"password=" + this.psw + ";" +
"Database=" + this.dataBase + ";";
break;
}
}
/// <summary>
/// (只读)SQL Server主机实例名称
/// </summary>
public string ServerName
{
get
{
return this.serverName;
}
}
/// <summary>
/// (只读)数据库名称
/// </summary>
public string DataBase
{
get
{
return this.dataBase;
}
}
/// <summary>
/// (只读)数据库连接字符串
/// </summary>
public string ConnctionString
{
get
{
return this.strConn;
}
} /// <summary>
/// 打开连接
/// </summary>
private void newConnection()
{
try
{
conn = new SqlConnection(this.strConn);
}
catch(SqlException e)
{
this.strError = e.Message;
this.Error(this,new EventArgs());
}
} /// <summary>
/// 关闭连接
/// </summary>
private void CloseConnection()
{
try
{
if(conn.State == ConnectionState.Open)
{
conn.Close();
}
}
catch(SqlException e)
{
this.strError = e.Message;
this.Error(this,new EventArgs());
}
} /// <summary>
/// 使用DataAdapter(数据适配器)对数据进行操作,并返回DataSet.
/// </summary>
/// <param name="SQL">SQL查询语句</param>
/// <param name="tableName">需要指定的表名</param>
/// <returns>DataSet数据集合</returns>
public DataSet getDataSet(string SQL,string tableName)
{
try
{
newConnection();
adapter = new SqlDataAdapter(SQL,this.strConn);
DataSet ds = new DataSet();
adapter.Fill(ds,tableName);
return ds;
}
catch(SqlException e)
{
this.strError = e.Message;
this.Error(this,new EventArgs());
}
return null;
} /// <summary>
/// 使用DataAdapter(数据适配器)对数据进行操作,并返回DataTable.
/// </summary>
/// <param name="SQL">SQL查询语句</param>
/// <param name="tableName">需要指定的表名</param>
/// <returns>DataTable数据表</returns>
public DataTable getDataTable(string SQL,string tableName)
{
try
{
newConnection();
adapter = new SqlDataAdapter(SQL,this.strConn);
DataTable dt = new DataTable(tableName);
adapter.Fill(dt);
return dt;
}
catch(SqlException e)
{
this.strError = e.Message;
this.Error(this,new EventArgs());
}
return null;
}
}
.net 我知道几种 Odbc,OleDb,SqlDb and so on.
每一种都可以在MSDN上查到怎样用。
Connection ,Command ,DataAdapter ,DataSet and so on
然后写你的SQL语句就可以了。
string strConn,strSQL;
strConn = "Data Source=local;Initial Catalog=dbname;User Id=sa;Password=aaa
strSQL = "select * from person ";
SqlDataAdapter acqda = new SqlDataAdapter(strSQL,strConn);
DataSet ds = new DataSet();
acqda.Fill(ds,"Cook");
修改:
strSql = "update table1 set field = a"
SqlConnection Cnn = new SqlConnection(strConn);
SqlCommand Cmd = new SqlCommand(strSql,Cnn);
Cmd.ExecuteNonQuery();