1、导入命名空间 using System.Data.SqlClient; //连接SQLServer 数据库专用 2、创建连接 SqlConnection lo_conn = New SqlConnection("Server=服务器名字或IP;Database=数据库名字;uid=用户名;pwd=密码"); 3、打开连接,第2步并没有真正连接数据库 lo_conn.Open(); //真正与数据库连接 4、向数据库发送SQL命令要使用SqlCommand: SqlCommand lo_cmd = new SqlCommand(); //创建命令对象 lo_cmd.CommandText = "这里是SQL语句"; //写SQL语句 lo_cmd.Connection = lo_con; //指定连接对象,即上面创建的 5、处理SQL命令或返回结果集 lo_cmd.ExecuteNonQuery(); //这个仅仅执行SQL命令,不返回结果集,实用于建表、批量更新等不需要返回结果的操作。 SqlDataReader lo_reader = lo_cmd.ExecuteReader();//返回结果集 6、以数据集的方式反回结果集 SqlDataAdapter dbAdapter = new SqlDataAdapter(lo_cmd); //注意与上面的区分开 DataSet ds = new DataSet(); //创建数据集对象 dbAdapter.Fill(ds); //用返回的结果集填充数据集,这个数据集可以被能操作数据的控件DataBind,其它的就自己发挥了吧 7、关闭连接 lo_conn.Close();上面是转了某位好友的,感觉写的不错,其实基本原理就是这样的,下面上个例子:
/// <summary>
/// 数据库操作处理器
/// </summary>
public class SQLProcessor
{
private SqlConnection conn;
public SqlConnection Conn
{
get
{
if (conn == null)
{
conn = ConnUtil.CreateConnection();//此处直接是一个连接串:"Initial Catalog=TOPJOIN;Data Source=130.1.0.15;User ID=sa;Password=topdj@123;Connect Timeout=30"
}
return conn;
}
set
{
conn = value;
}
}
/// <summary>
/// 执行sql
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet ExecuteQuery(string sql, SqlParameter[] parameters)
{
DataSet ds = new DataSet();
Conn.Open();
SqlCommand sc = new SqlCommand(sql, conn);
sc.Transaction = Conn.BeginTransaction();
try
{
if (!BaseUtil.isArrayEmpty(parameters))
{
sc.Parameters.AddRange(parameters);
}
SqlDataAdapter command = new SqlDataAdapter(sc);
command.Fill(ds);
}
catch (Exception e)
{
if (sc != null && sc.Transaction != null)
sc.Transaction.Rollback();
if (Conn != null)
Conn.Close();
throw new Exception(e.Message);
}
finally
{
if (sc != null && sc.Transaction != null)
sc.Transaction.Commit();
if (Conn != null)
Conn.Close();
}
return ds;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="spname"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataSet ExecuteStoredProcedure(string spname, SqlParameter[] parameters)
{
DataSet ds = new DataSet();
Conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;//设置cmd的类型为存储过程
cmd.CommandText = spname;
cmd.Connection = conn;
try
{
if (!BaseUtil.isArrayEmpty(parameters))
{
cmd.Parameters.AddRange(parameters);
}
SqlDataAdapter command = new SqlDataAdapter(cmd);
command.Fill(ds);
}
catch (Exception e)
{
if (Conn != null)
Conn.Close();
throw new Exception(e.Message);
}
finally
{
if (cmd != null && cmd.Transaction != null)
cmd.Transaction.Commit();
if (Conn != null)
Conn.Close();
}
return ds;
}
下面是一个调用的例子:
string sql = " select * from User where code = @Code ";
List<SqlParameter> lsp = new List<SqlParameter>();
lsp.Add(new SqlParameter("Code", code));//code为参数值哈
SQLProcessor sp = new SQLProcessor();
DataSet ds = sp.ExecuteQuery(sql, lsp.ToArray()) as DataSet;
或者这样用:
StringBuilder sb = new StringBuilder();
sb.Append(" select * from User ");
sb.Append(string.Format(" where code ='{0}' ",new String[]{code}));//code为参数值哈
SQLProcessor sp = new SQLProcessor();
DataSet ds = sp.ExecuteQuery(sb.ToString(),null) as DataSet;
好了,取到DataSet了,你就可以随便操作了!