鸿 网 互 联 www.68idc.cn

当前位置 : 服务器租用 > .net技术 > asp.net编程 > >

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

来源:互联网 作者:佚名 时间:2015-12-08 03:03
程序中添加了一个XML文件:App.config?xmlversion=1.0?configurationconfigSectionssectionGroupname=userSettingstype=System.Configuration.UserSettingsG..

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

<?xml version="1.0"?>

<configuration>

<configSections>

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

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

<section 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 connectionString=";Initial Catalog=Message;User ID=sa;PWD=112442;Connect Timeout=5; "/>

</connectionStrings>

<userSettings>

<Message.sysFrm>

<setting serializeAs="String">

<value>True</value>

</setting>

<setting serializeAs="String">

<value />

</setting>

<setting serializeAs="String">

<value />

</setting>

<setting serializeAs="String">

<value />

</setting>

</Message.sysFrm>

<Message.system>

<setting 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;         }     }      } 1.就是简单的想执行命令操作,如更新插入等等 string sqlstr = "insert into [Bank_Repairs](number,cardId,repairsTime,repairsType) values(@number,@cardId,@repairsTime,@repairsType)";                     SqlParameter[] param =                      {                                                        new SqlParameter("@number", SqlDbType.VarChar),                             new SqlParameter("@cardId",SqlDbType.NVarChar),                             new SqlParameter("@repairsTime", SqlDbType.DateTime),                             new SqlParameter("@repairsType", "终端自动")                      };                                          param[0].Value = bankId;                     param[1].Value = cardId;//非空字段  插入空值会引起错误                     param[2].Value = ntime;                     DBHelper.ExecuteNonQuery(sqlstr, param);  2.对数据库进行查询操作    string sqlStr = "select *,[Company].name as '公司名称',[Company].workItem as '工作内容',[Company].directorPhone as '主管电话',[Company].directorName as '主管姓名' from [User] join [Company] on [Company].number = [User].company ";             SqlDataReader dr = DBHelper.ExecuteReader(sqlStr, null);  3.查询数据库中是否有记录  string sqlStr = "select count(*) from [Bank_Repairs] where ([Bank_Repairs].repairsTime between @startTime and @endTime) and number = @bankId";             SqlParameter [] param = {                                   new SqlParameter("@bankId", bankId),                                   new SqlParameter("@startTime", startTime),                                   new SqlParameter("@endTime", endTime)                                   };             DateTime startTime = DateTime.Now.AddDays(-1);//查询2天之内的记录             DateTime endTime = DateTime.Now;             int res = Convert.ToInt32(DBHelper.ExecuteScalar(sqlStr, param));             isExist = res > 0 ? "ok" : "no";


网友评论
<