我刚开始学习使用数据库连接的CS程序,现在发觉1个问题。在每个button的Click代码中都需要写上:
SqlConnection con = new SqlConnection("server=服务器IP;database=数据库;uid=数据库帐号;pwd=数据库密码 ");
…………
等语句以后才能写其他执行语句。例如button1是登录按钮
我现在必须写上
SqlConnection con = new SqlConnection("server=.;database=Dyczbt;uid=sa;pwd= ");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText =以下是SQL代码;
具体执行语句;然后button2是修改密码按钮
还需要重复写上SqlConnection con = new SqlConnection("server=.;database=Dyczbt;uid=sa;pwd= ");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText =以下是SQL代码;
具体执行语句;
我虽然不知道该怎么解决,但是知道这样的方式肯定是不科学的。请教高手指点.
因为是自学 水平确实有限请高手们说的仔细点行吗?谢谢
SqlConnection con = new SqlConnection("server=服务器IP;database=数据库;uid=数据库帐号;pwd=数据库密码 ");
…………
等语句以后才能写其他执行语句。例如button1是登录按钮
我现在必须写上
SqlConnection con = new SqlConnection("server=.;database=Dyczbt;uid=sa;pwd= ");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText =以下是SQL代码;
具体执行语句;然后button2是修改密码按钮
还需要重复写上SqlConnection con = new SqlConnection("server=.;database=Dyczbt;uid=sa;pwd= ");
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText =以下是SQL代码;
具体执行语句;
我虽然不知道该怎么解决,但是知道这样的方式肯定是不科学的。请教高手指点.
因为是自学 水平确实有限请高手们说的仔细点行吗?谢谢
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = "server=.;database=; uid=;pwd=";
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
} public static int ExecuteCommand(params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = "Pro_UpdateBooksCatagory";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
return result;
} public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
} public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
return result;
} public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
} public static int GetScalar(params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = "Pro_InsertOrder";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
} public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
} public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
} public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
这基本上都能用 所有的方法 我也刚工作 我的号是852152961 共同研究
这一是安全,二是可靠。
不要怕偷懒,
实在不行就用CodeSnippet
把查询语句 更新删除插入语句 所用的方法独立写在一个类中 用不同的方法 然后根据需要调用。传入SQL语句
开发简单的用.net三层架构,学会简单的封装,自己的业务逻辑,处理自己的代码~
设计人:聂鑫 (FlyBee)
Copyrigth (c) 2009 FlyBee
创建日期:2009-3-6
修改者:
修改日期:
修改部分:
类功能:
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;/// <summary>
///DBClass 的摘要说明
///对数据库的所有操作都在这里,数据库的链接是私有的只能在这个类里打开数据库的链接,可以在其他类下关闭数据库的链接
/// </summary>
namespace Policy
{
class DBClass
{
private string ConStr;
/// <summary>
/// 构造连接字符串
/// </summary>
public DBClass() { ConStr = "Server=.;DataBase=Travel_Policy;Uid=sa;Pwd=sa;pooling=false"; } /// <summary>
/// 打开数据库
/// </summary>
/// <returns></returns>
#region//private SqlConnection DBOpen()
private SqlConnection DBOpen()
{
SqlConnection oleCon = new SqlConnection(ConStr);
oleCon.Open();
return oleCon;
} #endregion
/// <summary>
/// 关闭数据库
/// </summary>
/// <returns></returns>
#region//private SqlConnection DB_Close()
private SqlConnection DB_Close()
{
using (SqlConnection oleCon = new SqlConnection(ConStr))
{
oleCon.Close();
return oleCon;
}
}
#endregion
/// <summary>
/// 判断数据库如果不是关闭则将其关闭
/// </summary>
#region//public void DBClose()
public void DBClose()
{
using (SqlConnection oleCon = new SqlConnection(ConStr))
{
if (oleCon.State != ConnectionState.Closed) { DB_Close(); }
}
}
#endregion
/// <summary>
/// 更具一个Sql语句返回一个数据集
/// </summary>
/// <param name="str">Sql语句</param>
/// <returns>DataSet</returns>
#region//public DataSet GetDataSet(string str)
public DataSet GetDataSet(string str)
{
using (SqlDataAdapter dap = new SqlDataAdapter(str, DBOpen()))
{
DataSet ds = new DataSet();
dap.Fill(ds);
dap.Dispose();
DB_Close();
return ds;
}
}
#endregion
/// <summary>
/// 更具一个Sql语句返回一个表
/// </summary>
/// <param name="str">Sql语句</param>
/// <returns>DataTable</returns>
#region//public DataTable GetDataTable(string str)
public DataTable GetDataTable(string str)
{
using (SqlDataAdapter dap = new SqlDataAdapter(str, DBOpen()))
{
DataTable dt = new DataTable();
dap.Fill(dt);
dap.Dispose();
DB_Close();
return dt;
}
}
#endregion
/// <summary>
/// 判断查询是否通过
/// </summary>
/// <param name="str">Sql语句</param>
/// <returns>bool</returns>
#region//public int JudgeSql(string str)
public bool JudgeSql(string str)
{
try
{
int i = 0;
using (SqlCommand cmd = new SqlCommand(str, DBOpen()))
{
i = Convert.ToInt32(cmd.ExecuteScalar());
cmd.ExecuteReader().Close();
DB_Close();
}
if (i > 0) { return true; }
else { return false; }
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message, "系统提示", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Hand);
return false;
}
}
#endregion
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="strcmd">Sql语句</param>
/// <returns>bool</returns>
#region//public bool SqlCmd(string strcmd)
public bool SqlCmd(string strcmd)
{
try
{
using (SqlCommand cmd = new SqlCommand(strcmd, DBOpen()))
{
cmd.ExecuteNonQuery();
DB_Close();
return true;
}
}
catch
{
DB_Close();
return false;
}
}
#endregion
/// <summary>
/// 利用存储过程通过参数Id返回DataSet
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="id">ID</param>
/// <returns>DataSet</returns>
#region//public DataSet RunProcGetDataSet(string procName, int id)
public DataSet RunProcGetDataSet(string procName, int id)
{
using (SqlDataAdapter dap = new SqlDataAdapter(procName, DBOpen()))
{
dap.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter prams = new SqlParameter("@id", SqlDbType.VarChar, 50);
prams.Value = id;
dap.SelectCommand.Parameters.Add(prams);
DataSet ds = new DataSet();
dap.Fill(ds);
DB_Close();
return ds;
}
}
#endregion
/// <summary>
/// 更具一个Sql语句返回一个字符串
/// </summary>
/// <param name="str">Sql语句</param>
/// <returns>DataSet</returns>
#region//public string GetString(string str)
public string GetString(string str)
{
using (SqlDataAdapter dap = new SqlDataAdapter(str, DBOpen()))
{
DataSet ds = new DataSet();
dap.Fill(ds);
dap.Dispose();
DB_Close();
string s = null;
foreach (DataRow NewRow in ds.Tables[0].Rows)
{
s = NewRow[0].ToString();
}
return s;
}
}
#endregion
/// <summary>
/// 更具一个Sql语句返回一个数字
/// </summary>
/// <param name="str">Sql语句</param>
/// <returns>DataSet</returns>
#region//public int GetString(string str)
public int GetInt(string str)
{
using (SqlDataAdapter dap = new SqlDataAdapter(str, DBOpen()))
{
DataSet ds = new DataSet();
dap.Fill(ds);
dap.Dispose();
DB_Close();
int i = 0;
foreach (DataRow NewRow in ds.Tables[0].Rows)
{
i = Convert.ToInt32(NewRow[0]);
}
return i;
}
}
#endregion /// <summary>
/// 修改 DataGridview
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="_dt">要修改的数据表 DataTable</param>
/// <returns>修改完成的数据表 DataTable</returns>
#region//public DataTable UpGrid(string sql, DataTable _dt)
public DataTable UpGrid(string sql, DataTable _dt)
{
using (SqlDataAdapter dap = new SqlDataAdapter(sql, DBOpen()))
{
SqlCommandBuilder OdbBuilder = new SqlCommandBuilder(dap);
dap.UpdateCommand = OdbBuilder.GetUpdateCommand();
dap.Update(_dt);
_dt.Clear();
DataTable dt = new DataTable();
dap.Fill(dt);
dap.Dispose();
DB_Close();
return dt;
}
}
#endregion
}
}
封装!
顺便问一下 连接字符串写在config文件里面有没有什么弊端或者有什么优势
看了好都都没有写在config文件里面,我还是习惯在config文件里面写
然后封装 操作.
当你要用的时候你可以直接调用你面的函数。 而且当你要修改连接
的时候你也不用那样麻烦,直接修改这个LINK 类就OK了。
//链接字符串
public static readonly string ConnnectionString = ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString; //存储过程参数和值
public static string[] Params = null;
public static object[] Values = null; //错误信息
public static string ErrorString = "";
/// <summary>
/// 填充SqlCommand对象
/// </summary>
/// <param name="conn">SqlConnection 连接对象</param>
/// <param name="cmd">要填充的SqlCommand对象</param>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
protected static void PrepareCommand(SqlConnection conn, SqlCommand cmd, string strSqlOrProcedure, CommandType cmdType) {
cmd.Connection = conn;
cmd.CommandText = strSqlOrProcedure;
cmd.CommandType = cmdType; if (Params != null && Values != null && Params.Length == Values.Length) {
for (int i = 0; i < Params.Length; i++) {
SqlParameter sp = new SqlParameter();
sp.ParameterName = Params[i];
sp.Value = Values[i];
sp.Direction = ParameterDirection.Input; cmd.Parameters.Add(sp);
}
}
Params = null;
Values = null;
}
/// <summary>
/// 执行查询,只返回影响的行数
/// </summary>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回影响的行数</returns>
public static int ExecNonQuery(string strSqlOrProcedure, CommandType cmdType) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null; try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType); int nCount = cmd.ExecuteNonQuery();
cmd.Parameters.Clear(); return nCount;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close(); throw;
}
} /// <summary>
/// 执行查询,只返回第一行第一列的数据
/// </summary>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回第一行第一列的数据</returns>
public static object ExecScalar(string strSqlOrProcedure, CommandType cmdType) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null;
object val = null; try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType); val = cmd.ExecuteScalar();
cmd.Parameters.Clear(); return val;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close(); throw;
}
} public static SqlDataReader ExecReader(string strSqlOrProcedure, CommandType cmdType) {
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null; try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType); SqlDataReader sdr = cmd.ExecuteReader();
cmd.Parameters.Clear(); return sdr;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close(); throw;
}
} /// <summary>
/// 执行查询,并返回结果
/// </summary>
/// <param name="strSqlOrProcedure">要执行的Sql语句或存储过程</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回DataSet查询结果</returns>
public static DataSet ExecQuery(string strSqlOrProcedure, CommandType cmdType) {
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
SqlConnection conn = null; try {
conn = new SqlConnection(ConnnectionString);
conn.Open();
PrepareCommand(conn, cmd, strSqlOrProcedure, cmdType); SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
cmd.Parameters.Clear(); return ds;
}
catch (Exception e) {
ErrorString = e.Message;
conn.Close(); throw;
}
}
}