用C#。能写一个通用的SQL查询，插入，更新的类吗？

[shala99]

用C#。能写一个通用的SQL查询，插入，更新的类吗？

我要的是，不要用sqldatareader的噢，

谢谢，请详细一些，，，，

[koukoujiayi]

创建强类型的DataSet就是这样一个类(XSD)!
包含查询，插入，更新和删除的方法!!

[鸭梨山大帝]

http://www.microsoft.com/downloads/details.aspx?FamilyId=F63D1F0A-9877-4A7B-88EC-0426B48DF275&displaylang=en
SqlHelper
别用里面的SqlHelper.ExecuteReader,大Bug一个
其他的可以用

[wuyq11]

使用数据操作类sqlhelper.
通过泛型T实现实体类

[itliyi]

能 通用增删改查 分别对应一个方法。。。

[liangzhl]

可以啊，这么不行，呵呵

[智商无下限]

#region 自动更新并填充数据集（指定目标表方式）-by 智商无下限
        /// <summary>
        /// 自动更新并填充数据集（指定目标表方式）
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        /// <param name="selectText">查询语句</param>        
        /// <param name="dataTable">发生数据改变的表（通常写法为dataTable.GetChanges()）</param>
        /// <param name="cmdParms">参数列表</param>
        /// <returns>影响的行数</returns>
        /// <example> 具体例子如下.
        /// <code>
        ///private void button2_Click(object sender, EventArgs e)
        ///{
        ///    string connStr = "server=127.0.0.1; user id=sa; pwd=;database=pubs";
        ///    PHFLib.DbHelper dbHelper = new DbHelper(DbHelper.DataProviderType.SqlServer, connStr);
        ///    dbHelper.AutoUpdate(dbHelper.ConnString, "SELECT * FROM titles", dt.GetChanges(), null);
        ///}
        ///注：dt为通过dataGridview或其他方式改变的datatable
        /// </code>
        /// </example>
        public  int AutoUpdate(
            string connString,
            string selectText,
            DataTable dataTable,
            params DbParameter[] cmdParms
            )
        {
            int resultCount = 0;
            if (dataTable == null)
            {
                return 0;
            }
            DataTable dt = new DataTable();
            if (G_connection != null)
            {
                G_connection.Close();
            }
            this.G_connection.ConnectionString = connString;
            G_connection.Open();



            DbTransaction DbTransaction = G_connection.BeginTransaction();
               
                this.G_command.CommandText = selectText;
                G_command.Connection = G_connection;
                G_command.Transaction = DbTransaction;
                PrepareCommand(G_command, CommandType.Text, cmdParms);

                G_dataAdapter.SelectCommand = G_command;

                this.G_commandBuilder.DataAdapter = G_dataAdapter;
                try
                {
                    G_dataAdapter.Fill(dt);
                    dt.Merge(dataTable);
                    resultCount = G_dataAdapter.Update(dt);
                    DbTransaction.Commit();
                    return resultCount;
                }
                catch (Exception exp)
                {
                    DbTransaction.Rollback();
                    this.G_connection.Close();
                    
                    throw exp;
                }
           
        }

        #endregion

[abc402706746]

学习

[chxg99]

mark

[m_struggle]

可以

[Hamsic]

        private static SqlConnection conn = new SqlConnection();
        private static SqlCommand comm = new SqlCommand();
        private static SqlDataAdapter coda;
        private DataSet cods = new DataSet();

        private string _sqlword;
        public string sqlword
        {
            set { _sqlword = value; }
            get { return _sqlword; }
        }

        public SqlConnection GetSqlConnection()
        {
            string Connect = infoDB();
            SqlConnection conn = new SqlConnection(Connect);
            return conn;
        }

        public DataTable FillDt()
        {
            conn = GetSqlConnection();
            coda = new SqlDataAdapter(sqlword, conn);
            try
            {
                DataSet fillds = new DataSet();
                coda.Fill(fillds, "list");
                GC.Collect();
                return fillds.Tables["list"];
            }
            catch (Exception e)
            {
                System.Data.DataTable dt = new System.Data.DataTable();
                GC.Collect();
                return dt;
            }
        }

        public bool SQLExecute()
        {
            try
            {
                conn = GetSqlConnection();
                conn.Open();
                comm = new SqlCommand(sqlword, conn);
                comm.ExecuteNonQuery();
                conn.Close();
                GC.Collect();
            }
            catch (Exception e)
            {
                return false;
            }
            return true;
        }

        private string infoDB()
        {
            string strConnect = "server = .; user id = sa ; password = ****; database = Northwind";
            return strConnect;
        }

[jasper]

[Quote=引用 3 楼 wuyq11 的回复:]
使用数据操作类sqlhelper.
通过泛型T实现实体类
[/Quote]
非常庞大的设想

[kjiwu]

自己动手自力更生，自己写的，有的地方不成熟，呵呵

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

using System.Data.Common;
using System.Data;

namespace Ninja.Classes
{
    public enum DataBaseType
    {
        //System.Data.Odbc、System.Data.OleDb、System.Data.SqlClient、System.Data.SqlServerCe 和 System.Data.OracleClient
        ODBC=0,
        OLEDB,
        SQLCLIENT,
        SQLSERVERCE,
        ORACLECLIENT
    }

    //生成数据库对应的字符串
    public class DataBaseTypeFactory
    {
        public static string GetDataBaseTypeString(DataBaseType dbType)
        {
            string msg = null;
            switch (dbType)
            {
                case DataBaseType.ODBC:
                    msg = "System.Data.Odbc";
                    break;
                case DataBaseType.OLEDB:
                    msg = "System.Data.OleDb";
                    break;
                case DataBaseType.ORACLECLIENT:
                    msg = "System.Data.OracleClient";
                    break;
                case DataBaseType.SQLCLIENT:
                    msg = "System.Data.SqlClient";
                    break;
                case DataBaseType.SQLSERVERCE:
                    msg = "System.Data.SqlServerCe";
                    break;
                default:
                    msg = "System.Data.SqlClient";
                    break;
            }
            return msg;
        }
    }


    public class DataBaseHelper : IDisposable
    {
        private DbDataAdapter adapter;
        private DbConnection connection;
        private DbCommand command;
        private string dataBaseType;


        #region 属性定义
        public string ConnectionString
        {
            get;
            set;
        }
        public string CommandText
        {
            get;
            set;
        }
        public CommandType CmdType
        {
            get;
            set;
        }
        public string DBType
        {
            get
            {
                return dataBaseType;
            }
            set
            {               
                this.dataBaseType = value;
            }
        }
        #endregion


        #region 构造函数定义
        public DataBaseHelper(string dbType, CommandType cmdType)
        {
            this.dataBaseType = dbType;            
            this.ConnectionString = "";
            this.CmdType = cmdType;
            this.CommandText = "";            
        }
        public DataBaseHelper(string dbType)
            : this(dbType, CommandType.Text)
        {

        }

        public DataBaseHelper()
            : this(DataBaseType.SQLCLIENT.ToString())
        {
        }

        #endregion

        private void initializeDB()
        {
            try
            {
                DbProviderFactory factory = DbProviderFactories.GetFactory(this.dataBaseType);
                this.connection = factory.CreateConnection();
                this.command = factory.CreateCommand();
                this.adapter = factory.CreateDataAdapter();
            }
            catch (DbException e)
            {
                throw e;
            }
        }

        public void ConnectAndOpen()
        {
            if (this.ConnectionString == String.Empty)
            {
                throw new ArgumentException("ConnectionString is not allow empty.", "ConnectionString");
            }
            //初始化数据库相关组件      
            initializeDB();
            
            this.connection.ConnectionString = this.ConnectionString;

            try
            {
                this.connection.Open();
            }
            catch (DbException e)
            {
                throw e;
            }
        }

        private void commandInitialize(DbParameter[] parameters)
        {
            this.command.Connection = this.connection;
            this.command.CommandText = this.CommandText;
            this.command.CommandType = this.CmdType;
            if (null != parameters)
            {
                this.command.Parameters.AddRange(parameters);
            }
        }

        public int ExecuteNonQuery(DbParameter[] parameters)        
        {
            commandInitialize(parameters);

            int effectRows = 0;
            try
            {
                effectRows = this.command.ExecuteNonQuery();
            }
            catch(DbException e)
            {
                throw e;
            }

            return effectRows;
        }

        public DataSet Execute(DbParameter[] parameters, string tableName)
        {
            commandInitialize(parameters);
            DataSet ds = new DataSet();

            try
            {
                this.adapter.SelectCommand = this.command;
                this.adapter.Fill(ds, tableName); 
            }
            catch (SystemException e)
            {
                throw e;
            }
            return ds;
        }

        public void Close()
        {
            if (this.connection.State == ConnectionState.Open)
                this.connection.Close();
        }

        public void Dispose()
        {            
            if (this.connection.State == ConnectionState.Open)
                this.connection.Close();
        }
    }
}

[vip__888]

Sqlhelper

[phoeni_xin]

[vip__888]

[Quote=引用 2 楼 lost_painting 的回复:]
http://www.microsoft.com/downloads/details.aspx?FamilyId=F63D1F0A-9877-4A7B-88EC-0426B48DF275&displaylang=en
SqlHelper
别用里面的SqlHelper.ExecuteReader,大Bug一个
其他的可以用
[/Quote]
bug 在什么地方

[anya]

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

/// <summary>
/// Connect 的摘要说明
/// </summary>
/// <summary>
/// 数据库操作类
/// </summary>
public class Conn
{
public static SqlConnection conn=new SqlConnection(ConfigurationSettings.AppSettings["SQLConnectionString"]); //静态构造一个数据库打开的连接,ConfigurationSettings.AppSettings[0]为web.config配置文件中声明的数据库连接字符串


public static DataTable BindTable(string sqlstr) //静态方法；参数sqlstr为数据库查询语句,将从数据库中获得的数据填充到一个DataTable中，返回该DataTable
{
SqlDataAdapter cmd=new SqlDataAdapter(sqlstr,conn);
DataTable dt=new DataTable();
cmd.Fill(dt);
return dt;
}

public static DataTable BindTable(string sqlstr,string condition) //静态方法；参数sqlstr为数据库查询语句,参数condition为查询条件,将从数据库中获得的数据填充到一个DataTable中，返回该DataTable
{
SqlDataAdapter cmd=new SqlDataAdapter(sqlstr+condition,conn);
DataTable dt=new DataTable();
cmd.Fill(dt);
return dt;
}

public static SqlDataReader BindReader(string sqlstr) //静态方法；参数sqlstr为数据库查询语句,将SqlDataReader指向从数据库中获得的数据，返回该SqlDataReader
{
SqlCommand cmd=new SqlCommand(sqlstr,conn);
SqlDataReader dr=cmd.ExecuteReader();
return dr;
}

public static SqlDataReader BindReader(string sqlstr,string condition) //静态方法；参数sqlstr为数据库查询语句,参数condition为查询条件,将SqlDataReader指向从数据库中获得的数据，返回该SqlDataReader
{
SqlCommand cmd=new SqlCommand(sqlstr+condition,conn);
SqlDataReader dr=cmd.ExecuteReader();
return dr;
}

public static void ExecuteSql(string sql)
{

conn.Open();
SqlCommand myCmd=new SqlCommand(sql,conn);
myCmd.ExecuteNonQuery();
conn.Close();

}
}



调用方法

Datatable dt = Connect.BindTable("SELECT * from table");

[anya]

不好意思,调用写错了
调用方法

Datatable dt = Conn.BindTable("SELECT * from table");

[longe329]

关注，我也想这么写 不过不会

[鸭梨山大帝]

其版本V1中有大BUG,V2中修正了.呵呵

[Quote=引用 15 楼 vip__888 的回复:]
引用 2 楼 lost_painting 的回复:
http://www.microsoft.com/downloads/details.aspx?FamilyId=F63D1F0A-9877-4A7B-88EC-0426B48DF275&displaylang=en
SqlHelper
别用里面的SqlHelper.ExecuteReader,大Bug一个
其他的可以用

bug 在什么地方
[/Quote]