求个c#操作sql数据库的类

[zixing]

请顺便告诉一下如何使用.新手求教.请详细一点.多谢.
另外问一下.操作数据库的时候,比如有三个函数,分别执行查询,更新,检查功能,这三个函数是公用一个conn,一个cmd.还是在每个函数中都定义一个?

[磨砻淬砺]

路过看看

[zixing]

也就是说.在每个函数内部定义sqlconnection与sqlcommand?在函数的结束,将sqlconnection关闭掉?

[老毕]

[Quote=引用 12 楼 zixing 的回复:]

也就是说.在每个函数内部定义sqlconnection与sqlcommand?在函数的结束,将sqlconnection关闭掉?
[/Quote]
嗯，使用SqlCommand时，如果遇到SQL语句有查询参数，请用SqlParameter，不要用拼接字符串的方法。后者既不安全，也不好看。

[zixing]

好的.多谢多谢.
我贴上段代码.你看一下.看看有没有问题.

[zixing]

private int maxi_serial()
        {
            /////取oplogs的i_serial的最大值
            SqlConnection strcon = new SqlConnection("server=192.168.1.10;database=money;uid=sa;pwd=sa;");
            strcon.Open();
            SqlCommand scd = new SqlCommand("select max(i_serial)+1 as maxi_serial from now..oplogs", strcon);
            int max_serial = Convert.ToInt32(scd.ExecuteScalar());
            strcon.Close();
            //
            listBox1.Items.Add("取得最大值为" + Convert.ToString(max_serial));
            return max_serial;

        }
        //
        private void checksave(string bank, int userid, string orderno)
        {
            //////////now查询是否已经存入金额成功,rj成功则将是否成功标志置为1
            SqlConnection cxcon = new SqlConnection("server=192.168.1.10;database=now;uid=sa;pwd=sa;");
            cxcon.Open();
            SqlCommand cxcd = new SqlCommand("select count(*) from oplogs where v_summary = '" + orderno + "'", cxcon);
            int rescou = Convert.ToInt32(cxcd.ExecuteScalar());
            cxcon.Close();

            if (rescou == 1)
            {
                ////已经存入,可将rj..successed置为1
                
                SqlConnection updatecon = new SqlConnection("server=192.168.1.10;database=rj;uid=sa;pwd=sa;");
                updatecon.Open();
                SqlCommand updatecd;
                if (bank == "ccb")
                {
                    updatecd = new SqlCommand("update ccborder set successed = 1 where orderno='" + orderno + "'", updatecon);
                }
                else
                {
                    updatecd = new SqlCommand("update abcorder set successed = 1 where orderno='" + orderno + "'", updatecon);
                }
                updatecd.ExecuteScalar();
                updatecon.Close();
                //
                listBox1.Items.Add("更新完毕");
            }
            else if (rescou > 1)
            {
                listBox1.Items.Add("检测到编号为:" + orderno + "的订单有相同的存入记录");
            }


        }
        //
        private void checksave(string bank)
        {
            //////////////检测一下.是否有已经存入但是successed并没有置为1的记录
            SqlConnection cfcon = new SqlConnection("server=192.168.1.10;database=rj;uid=sa;pwd=sa;");
            SqlCommand cfcd;
            if (bank == "ccb")
            {
                cfcd = new SqlCommand("update ccborder set successed = 1 where orderno in (select orderno from ccborder where successed = 0 and orderno in (select v_summary from now..oplogs))", cfcon);
            }
            else
            {
                cfcd = new SqlCommand("update abcorder set successed = 1 where orderno in (select orderno from abcorder where successed = 0 and orderno in (select v_summary from now..oplogs))", cfcon);
            }
            cfcd.ExecuteScalar();
            cfcon.Close();
        }

[zixing]

没个函数中都定义了一个sqlconnection.和一个sqlcommand.
然后函数完毕以后.关闭连接.
这样写合适吗?

[老毕]

1. 在同一个方法内部，Connection可以重用，所以上面 checksave()里的Connection不用重复构造多个。
2. 对应SQL查询的语句最好单独拿出来声明成一个字符串变量，方便阅读
3. Command对象也可以通过改变其CommandText等属性重用。

大概差不多了

[Name_456]

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Data.Common;
namespace EstanDAL
{
public class SQLHelper
{

//Database connection strings
public static readonly string SQLConnString = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
// Hashtable to store cached parameters
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();

using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
} public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 获得datatable

public static DataTable ExecuteReaderDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
DataSet dataset = new DataSet();
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, conn);

adapter.Fill(dataset);
cmd.Parameters.Clear();
}
catch (Exception e)
{

throw;
}
finally
{
conn.Close();
} return dataset.Tables[0];
}
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);

// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}

public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();

using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}



public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{

SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}


public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}

public static SqlParameter[] GetCachedParameters(string cacheKey)
{
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

if (cachedParms == null)
return null;

SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

return clonedParms;
}

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{

if (conn.State != ConnectionState.Open)
conn.Open();

cmd.Connection = conn;
cmd.CommandText = cmdText;

if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
}

[litianci2002]

不必建那么多sqlconnection吧，使用的时候，判断一下sqlconnection是不是null，就行了，可以在关闭窗体的时候，判断一下 sqlconnection ，不为空，关闭，释放，置为null

[lrcitpower]

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

namespace database
{
public class DataBase
{

/// <summary>
/// 保护变量，数据库连接。
/// </summary>
protected SqlConnection Connection;

/// <summary>
/// 保护变量，数据库连接串。
/// </summary>
protected string ConnectionString;

/// <summary>
/// 构造函数。
/// </summary>
/// <param name="DataBaseConnectionString">数据库连接串</param>
public DataBase()
{
ConnectionString = ConfigurationManager.AppSettings["ConnectString"];
}

/// <summary>
/// 析构函数，关闭数据库
/// </summary>
~DataBase()
{
try
{
if (Connection != null)
Connection.Close();
}
catch { }
}

/// <summary>
/// 保护方法，打开数据库连接。
/// </summary>
protected void Open()
{
if (Connection == null)
{
Connection = new SqlConnection(ConnectionString);
}
if (Connection.State.Equals(ConnectionState.Closed))
{
Connection.Open();
}
}

/// <summary>
/// 公有方法，关闭数据库连接。
/// </summary>
public void Close()
{
if (Connection != null)
Connection.Close();
}

/// <summary>
/// 公有方法，获取数据，返回一个DataSet。
/// </summary>
/// <param name="SqlString">Sql语句</param>
/// <returns>DataSet</returns>
public DataSet GetDataSet(string SqlString)
{
Open();
SqlDataAdapter adapter = new SqlDataAdapter(SqlString, Connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Close();
return dataset;
}
/// <summary>
/// 公有方法，获取数据，返回一个DataReader。
/// </summary>
/// <param name="SqlString">Sql语句</param>
/// <returns>DataSet</returns>
public SqlDataReader GetDataReader(string SqlString)
{
Open();
SqlCommand cmm = new SqlCommand(SqlString, Connection);
SqlDataReader datareader = cmm.ExecuteReader();
return datareader;
}
/// <summary>
/// 公有方法，执行数据库操作。
/// </summary>
public void databaseExcutive(string SqlString)
{
Open();
SqlCommand Mycomm = new SqlCommand(SqlString, Connection);
Mycomm.ExecuteNonQuery();
Close();
}
///<summary>
///公有方法,数据库查询带子参数查询
///</summary>
public DataSet GetDataSetSql(string tableName, string filter)
{
Open();
string sqlstr = "select * from " + tableName + " " + filter;
SqlDataAdapter adapter = new SqlDataAdapter(sqlstr, Connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Close();
return dataset;
}
/// 自定义数据查询
/// </summary>
/// <param name="displayfilter">查询字段</param>
/// <param name="filter">查询筛选条件</param>
/// <returns>DataSet类型</returns>
public DataSet GetDataSetKeySql(string table, string field, string filter)
{
Open();
string sqlstr = "select " + field + " from " + table + " " + filter;
SqlDataAdapter adapter = new SqlDataAdapter(sqlstr, Connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
Close();
return dataset;
}
/// <summary>
/// 数据插入
/// </summary>
/// <param name="filedname">字段名称</param>
/// <param name="editfildename">值</param>
/// <returns>Bool类型</returns>
public bool dataAdd(string tablename, string fieldname, string editfieldname) //数据插入
{
Open();
string sqlstr = "insert into " + tablename + " (" + fieldname + ") values(" + editfieldname + ")";
databaseExcutive(sqlstr);
bool state;
state = true;
return state;
}


/// <summary>
/// 数据删除
/// </summary>
/// <param name="filter">删除条件</param>
/// <returns>Bool类型</returns>
public bool dataDelete(string tablename, string filter) //数据删除
{
Open();
string sqlstr = "delete from " + tablename + filter;
databaseExcutive(sqlstr);
bool state;
state = true;
return state;
} /// <summary>
/// 数据更新
/// </summary>
/// <param name="filter">删除条件</param>
/// <returns>Bool类型</returns>
public bool dataupdate(string tablename, string field, string filter) //数据删除
{
Open();
string sqlstr = "UPDATE " + tablename + " set " + field + " where " + filter;
databaseExcutive(sqlstr);
bool state;
state = true;
return state;
}
///access
///
public DataSet GetAccessSet(string ocon, string SqlString)
{
OleDbConnection odc = new OleDbConnection(ocon);
OleDbDataAdapter odapter = new OleDbDataAdapter(SqlString, odc);

DataSet dataset = new DataSet();
odapter.Fill(dataset);
Close();
return dataset;
}
}
}


我正在用的一个数据库操作的类，你应该可以用到。。。

[wsd_asp]

看下五个对象

[daifei4451]

看看这里

[zixing]

还请问一下.这个东西咋是用啊.
我新建一个类.贴上代码.
在窗体中使用这些函数时,怎么不识别呢

[qldsrx]

新手建议用.NET自带的类，别用第三方包装过的Helper，因为别人根据自己的需要包装的类，往往会有BUG，又不负责维护，你自己没能力的话也改不了，出了问题也不好找。如果你有能力了，借鉴别人，自己封装一个数据库操作类，这样一旦功能上有不足之处，自己也会查错修改。

关于连接和命令对象，如果是同一个函数中请重复利用，如果是不同的函数还是用完释放，因为你不知道什么时候还会再用到，一直不释放不好吧。其实如果自己写函数，完全可以把所谓的conn和cmd参数从外部传递，这样就实现了多个函数共用一个连接和命令了（其实是为了建立事务，让所有操作在一个事务中执行，出错一起回滚）。

[py1025677845]

mark

[AK47]

学习。。。。。。。。。。。

[熙风]

public class DBHelper
    {
        //属性:数据库链接对象
        private static SqlConnection conn;
        public static SqlConnection Conn
        {
            get
            {
                string connstr =“server=192.168.1.10;database=rj;uid=sa;pwd=sa;”// ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString.ToString();
                
                if (conn == null)
                    conn = new SqlConnection(connstr);
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                if (conn.State == ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                }
                return conn;
            }
        }


        //方法:查询,DataReader
        public static SqlDataReader GetReader(string SqlStr)
        {
            SqlCommand cmd = new SqlCommand(SqlStr, Conn);
            return cmd.ExecuteReader();
        }

        public static SqlDataReader GetReader(string SqlStr, SqlParameter[] paras)
        {
            SqlCommand cmd = new SqlCommand(SqlStr, Conn);
            cmd.Parameters.AddRange(paras);
            return cmd.ExecuteReader();
        }


        //查询:DataTable
        public static DataTable GetTable(string SqlStr)
        {
            SqlDataAdapter dap = new SqlDataAdapter(SqlStr, Conn);
            DataSet ds = new DataSet();
            dap.Fill(ds);
            conn.Close();
            return ds.Tables[0];

        }

        //增删改
        public static bool Execute(string SqlStr)
        {
            SqlCommand cmd = new SqlCommand(SqlStr, Conn);
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            return result > 0;
        }

        //返回首行首列
        public static object GetScalar(string SqlStr)
        {
            SqlCommand cmd = new SqlCommand(SqlStr, Conn);
            object obj = cmd.ExecuteScalar();
            conn.Close();
            return obj;
        } 

[Wicho开源开发中]

这些函数要通过调用的#109 请LZ 先了解 下N-layer 再来做这个项目吧

[zixing]

我晓得是通过调用啊.我的意思就是调用的时候.找不到
一般不是这样用吗?
DataTable dt = DBHelper.GetTable("select * from users");
这样吧?
但是提示找不到此函数...

[zixing]

搞懂了.其实简单的很.就是里面多了个大括号.