执行sql语句代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】执行sql语句
作者:忽左忽右 / 发布于2014/9/10/ 696
通过app.config文件配置数据库连接串，传参更加人性化，可阅读性强，操作简单。

	app.config文件配置

	<?xml version="1.0" encoding="utf-8" ?>
	<configuration>
	 <configSections>
	 <section name="dataConfiguration"
	 type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data"/>
	 </configSections>
	 <appSettings>
	 <add key="StrConn" value="Provider=msdaora;Password=emr;pwdSecurity Info=True;User ID=uname;Data Source=database" />
	 </appSettings>
	</configuration>

	

	CommonFucntion中添加ExcuteSql()和ExcuteNonQuery方法

	/// <summary>
	 /// 执行查询操作
	 /// </summary>
	 /// <param name="sql">查询sql语句</param>
	 /// <param name="dictionary">字典参数</param>
	 /// <param name="tablename">产生的datatable名称</param>
	 /// <param name="cmd">cmd</param>
	 /// <returns>返回表</returns>
	 static public DataTable ExecuteBySQL(string sql, Dictionary<string, string> dictionary, string tablename)
	 {
	 OleDbConnection conn = new OleDbConnection(GetConnString());
	 OleDbCommand cmd = conn.CreateCommand();
	 conn.Open();
	 DataTable table = new DataTable(tablename);
	 ChangeSelectCommand(sql, dictionary, ref cmd);
	 try
	 {
	 using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
	 {
	 adapter.Fill(table);
	 }
	 }
	 catch (Exception e)
	 {
	 e.ToString();
	 }
	 finally
	 {
	 conn.Dispose();
	 cmd.Dispose();
	 }
	
	 return table;
	 }
	
	 /// <summary>
	 /// 执行增，删，改操作
	 /// </summary>
	 /// <param name="sql">操作的sql</param>
	 /// <param name="dictionary">字典参数</param>
	 /// <param name="cmd">cmd</param>
	 /// <returns>返回结果</returns>
	 static public int ExecutenonQuery(string sql, Dictionary<string, string> dictionary)
	 {
	 OleDbConnection conn = new OleDbConnection(GetConnString());
	 OleDbCommand cmd = conn.CreateCommand();
	 conn.Open();
	 int n = 0;
	 ChangeSelectCommand(sql, dictionary, ref cmd);
	 try
	 {
	 n = cmd.ExecuteNonQuery();
	 }
	 catch (Exception ex)
	 {
	 n = 0;
	 }
	 finally
	 {
	 conn.Dispose();
	 cmd.Dispose();
	 }
	 return n;
	 }
	
	 private static string GetConnString()
	 {
	 string strconn = System.Configuration.ConfigurationSettings.AppSettings["StrConn"].ToString();
	 return strconn;
	 }
	
	 /// <summary>
	 /// 替换sql语句参数，并给cmd赋值
	 /// </summary>
	 /// <param name="sql">sql语句</param>
	 /// <param name="dictionary">参数字典</param>
	 /// <param name="cmd">cmd</param>
	 /// <returns>返回是否替换参数成功</returns>
	 static public bool ChangeSelectCommand(string sql, Dictionary<string, string> dictionary, ref OleDbCommand cmd)
	 {
	
	 cmd.Parameters.Clear();
	 string sqltxt = sql;
	 int nIndex = sqltxt.IndexOf('@');
	 while (-1 != nIndex)
	 {
	 if (nIndex > -1)
	 {
	 foreach (object obj in dictionary.Keys)
	 {
	 string strParm = "@" + obj.ToString();
	 if (nIndex == sqltxt.IndexOf(strParm, nIndex))
	 {
	 string values;
	 dictionary.TryGetValue(obj.ToString(), out values);
	 cmd.Parameters.Add(nIndex.ToString(), OleDbType.VarChar).Value = values;
	 }
	 }
	 }
	 if (sqltxt.Length > nIndex)
	 {
	 nIndex = sqltxt.IndexOf('@', nIndex + 1);
	 }
	 else
	 nIndex = -1;
	 }
	 if (dictionary != null)
	 {
	 foreach (object obj in dictionary.Keys)
	 {
	 string strParm = "@" + obj.ToString();
	 sqltxt = sqltxt.Replace(strParm, "?");
	 }
	 }
	 cmd.CommandText = sqltxt;
	 return true;
	 }
	
	 #endregion
试试其它关键字
　执行sql语句　
同语言下
可能有用的
忽左忽右贡献的其它代码(4)