C# sql 传参 Adaptor方式

最新推荐文章于 2023-10-15 18:07:31 发布

转载最新推荐文章于 2023-10-15 18:07:31 发布 · 665 阅读

其他专栏收录该内容

7 篇文章

订阅专栏

本文介绍使用ADO.NET进行数据库查询的具体实现方式，包括连接数据库、执行SQL语句、使用参数化查询并返回查询结果到DataSet中。通过示例代码展示了如何在C#中操作数据库。

1./// 执行查询语句，返回DataSet
OleDbConnection m_Conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\demo.mdb;Persist Security Info=True");
m_Conn.Open();
            OleDbDataAdapter  m_Adapter = new OleDbDataAdapter();
            m_Adapter.SelectCommand = new OleDbCommand("select * from T_CodeSet_Item where CodeSet_ID=?",m_Conn);
            m_Adapter.SelectCommand.Parameters.Add("@CodeSet_ID",OleDbType.VarChar,50);
m_Adapter.SelectCommand.Parameters[0].Value="AB";

DataSet ds = new DataSet();
m_Adapter.Fill(ds,"数据");

this.dataGrid1.DataSource = ds;

/// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, 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 = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }