c#关于数据库连接操作的案例

程序中添加了一个XML文件：App.config

<?xml version="1.0"?>

<configuration>

  <configSections>

    <sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">

      <section name="Message.sysFrm" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false"/>

      <section name="Message.system" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false"/>

    </sectionGroup>

  </configSections>

  <startup>

    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>

  </startup>

  <connectionStrings>

    <add name="connString" connectionString=";Initial Catalog=Message;User ID=sa;PWD=112442;Connect Timeout=5; "/>

  </connectionStrings>

  <userSettings>

    <Message.sysFrm>

      <setting name="sysFirstStart" serializeAs="String">

        <value>True</value>

      </setting>

      <setting name="comSetting" serializeAs="String">

        <value />

      </setting>

      <setting name="portSetting" serializeAs="String">

        <value />

      </setting>

      <setting name="serverSetting" serializeAs="String">

        <value />

      </setting>

    </Message.sysFrm>

    <Message.system>

      <setting name="comSetting" serializeAs="String">

        <value/>

      </setting>

    </Message.system>

  </userSettings>

</configuration>

namespace Message
{
    class DBHelper
    {
        private static sysFrm sysSetting = new sysFrm();//定义应用程序设置文件
        public static readonly string connStr = "Data Source=" + sysSetting.serverSetting + System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
        //
        public static int ExecuteNonQuery(string sqlStr, params SqlParameter[] param)//ExecuteNonQuery返回受语句影响的行数，如果是select语句则返回-1；
        {
            int res = 0;
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = connStr;
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = sqlStr;
                    cmd.Connection = conn;
                    if (param != null)
                        cmd.Parameters.AddRange(param);//另一个 SqlParameterCollection 中已包含 SqlParameter。
                    // 摘要：向 System.Data.SqlClient.SqlParameterCollection 的末尾添加values的数组。
                    // 参数:values:要添加的 System.Data.SqlClient.SqlParameter 值。
                    
                    try
                    {
                        conn.Open();
                        res = cmd.ExecuteNonQuery();
                    }
                        
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    //cmd.Parameters.Clear();//另一个 SqlParameterCollection 中已包含 SqlParameter。可以解决报错问题，但是是否影响性能还有待验证
                    return res;
                }
            }
        }

        public static object ExecuteScalar(string sqlStr, params SqlParameter[] param)//执行查询，并返回查询结果的第一行和第一列
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = connStr;
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = sqlStr;
                    if (param != null)
                        cmd.Parameters.AddRange(param);
                    conn.Open();//打开数据库
                   
                    return cmd.ExecuteScalar();
                    

                }
            }
        }

        public static SqlDataReader ExecuteReader(string sqlStr, params SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connStr;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = sqlStr;
                if (param != null)
                    cmd.Parameters.AddRange(param);
                

                try
                {
                    conn.Open();               
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("获取网络内数据库服务器和实例出错！"  + ex.ToString(), "提示",MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return null;

                }
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
        }

        public static DataTable DataAdapter(string sqlstr, params SqlParameter[] param)
        {     
            SqlDataAdapter da = new SqlDataAdapter(sqlstr, connStr);
            DataTable dt = new DataTable();
            da.SelectCommand.Parameters.AddRange(param);
            //DataSet ds = new DataSet();
            da.Fill(dt);
            int ias =   dt.Rows.Count;
            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                DataRow dr = dt.Rows[i];
            }
            return dt;
        }
    }
    
}

本文出自 “涛笙依旧” 博客，请务必保留此出处http://wangwentao2015.blog.51cto.com/5601365/1689848

c#关于数据库连接操作的案例

标签：c# 连接数据库 c#对数据库的操作