我一般这么写
回去慢慢研究
我刚学的时候
研究了很...........................很...............................很长很长的时间
但最终我还是搞明白了//引入命名空间
using System.Data.SqlClient;/// <summary>
/// SqlHelper 的摘要说明
/// </summary>
public class SqlHelper
{
private static readonly string sqlcon = ConfigurationManager.ConnectionStrings["enterpriseCon"].ConnectionString; //建立连接方法
public SqlConnection createCon()
{
SqlConnection con = new SqlConnection(sqlcon);
if (con.State != ConnectionState.Open)
con.Open();
return con;
}
//准备command命令
public static void prepareCommand(SqlConnection con, SqlCommand cmd, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] para)
{
//如果数据库连接没有打开 则打开连接
if (con.State != ConnectionState.Open)
{
con.Open();
}
cmd.Connection = con;
cmd.CommandText = cmdText;
if (trans != null)//如果trans存在的话 执行以下命令
{
cmd.Transaction = trans;
}
cmd.CommandText = cmdText;
if (para != null)//如果para不为空 遍历一遍para
{
foreach (SqlParameter pa in para)
{
cmd.Parameters.Add(pa);
}
}
} //从数据库中读取数据源 返回一个SqlDataReader对象
public static SqlDataReader executeReader(string cmdText, CommandType cmdType, SqlParameter[] para)
{
//定义连接
SqlConnection con = new SqlConnection(sqlcon);
//捕获可能发生的异常
try
{
SqlCommand cmd = new SqlCommand();
prepareCommand(con, cmd, null, cmdType, cmdText, para); SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return sdr;
}
catch (Exception)
{
throw;
}
} //更新数据库方法(增加,删除)
public static int executeNonQuery(string cmdText, CommandType cmdType, SqlParameter[] para)
{
using (SqlConnection con = new SqlConnection(sqlcon))
{
try
{
SqlCommand cmd = new SqlCommand();
prepareCommand(con, cmd, null, cmdType, cmdText, para);
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return result;
}
catch (Exception)
{
throw;
}
}
} //取得数据库中一行的值
public static string executeScalar(string cmdText, CommandType cmdType, SqlParameter[] para)
{
using (SqlConnection con = new SqlConnection(sqlcon))
{
try
{
SqlCommand cmd = new SqlCommand();
prepareCommand(con, cmd, null, cmdType, cmdText, para);
string result = Convert.ToString(cmd.ExecuteScalar());
cmd.Parameters.Clear();
return result;
}
catch (Exception)
{ throw;
}
}
} //DataSet
public static DataSet executeDataAdapter(string cmdText,CommandType cmdType,SqlParameter[] para) {
using (SqlConnection con=new SqlConnection(sqlcon))
{
try
{
SqlCommand cmd = new SqlCommand();
prepareCommand(con, cmd, null, cmdType, cmdText, para);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch (Exception)
{
throw;
}
}
}
}
回去慢慢研究
我刚学的时候
研究了很...........................很...............................很长很长的时间
但最终我还是搞明白了//引入命名空间
using System.Data.SqlClient;/// <summary>
/// SqlHelper 的摘要说明
/// </summary>
public class SqlHelper
{
private static readonly string sqlcon = ConfigurationManager.ConnectionStrings["enterpriseCon"].ConnectionString; //建立连接方法
public SqlConnection createCon()
{
SqlConnection con = new SqlConnection(sqlcon);
if (con.State != ConnectionState.Open)
con.Open();
return con;
}
//准备command命令
public static void prepareCommand(SqlConnection con, SqlCommand cmd, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] para)
{
//如果数据库连接没有打开 则打开连接
if (con.State != ConnectionState.Open)
{
con.Open();
}
cmd.Connection = con;
cmd.CommandText = cmdText;
if (trans != null)//如果trans存在的话 执行以下命令
{
cmd.Transaction = trans;
}
cmd.CommandText = cmdText;
if (para != null)//如果para不为空 遍历一遍para
{
foreach (SqlParameter pa in para)
{
cmd.Parameters.Add(pa);
}
}
} //从数据库中读取数据源 返回一个SqlDataReader对象
public static SqlDataReader executeReader(string cmdText, CommandType cmdType, SqlParameter[] para)
{
//定义连接
SqlConnection con = new SqlConnection(sqlcon);
//捕获可能发生的异常
try
{
SqlCommand cmd = new SqlCommand();
prepareCommand(con, cmd, null, cmdType, cmdText, para); SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return sdr;
}
catch (Exception)
{
throw;
}
} //更新数据库方法(增加,删除)
public static int executeNonQuery(string cmdText, CommandType cmdType, SqlParameter[] para)
{
using (SqlConnection con = new SqlConnection(sqlcon))
{
try
{
SqlCommand cmd = new SqlCommand();
prepareCommand(con, cmd, null, cmdType, cmdText, para);
int result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return result;
}
catch (Exception)
{
throw;
}
}
} //取得数据库中一行的值
public static string executeScalar(string cmdText, CommandType cmdType, SqlParameter[] para)
{
using (SqlConnection con = new SqlConnection(sqlcon))
{
try
{
SqlCommand cmd = new SqlCommand();
prepareCommand(con, cmd, null, cmdType, cmdText, para);
string result = Convert.ToString(cmd.ExecuteScalar());
cmd.Parameters.Clear();
return result;
}
catch (Exception)
{ throw;
}
}
} //DataSet
public static DataSet executeDataAdapter(string cmdText,CommandType cmdType,SqlParameter[] para) {
using (SqlConnection con=new SqlConnection(sqlcon))
{
try
{
SqlCommand cmd = new SqlCommand();
prepareCommand(con, cmd, null, cmdType, cmdText, para);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch (Exception)
{
throw;
}
}
}
}
解决方案 »
- asp.net 全局缓存 想实现如下的功能
- 点击重试才能刷新
- 一个非常棒的电子商务网站,技术和运营方式与众不同
- 大家在往数据库表里添加数据时用什么控件比较合理?
- OleDbDataAdapter 查询带条件就导致DataSet的Table[0]中无数据
- csdn上还没有人问的“配置错误”问题,需要重新启动才可以,请教高手。
- 新新手问:我怎么把查询的结果放到Label控件上
- 求一个VS2003的上传组件???
- 新手问题!请帮忙解答!高手来拿分!谢谢!
- aspx页面用html来写的,里面的控件,怎么触发aspx.cs中的方法
- 用sqlhelper怎么连接数据库?
- 数据库存分页 搜索 (支持搜索时能够接收参数的,找了几个月都没有找到,请高手发发善心)
"Data Source=.;Initial Catalog=数据库;User ID=sa;Password=密码;";
sql2005 express版本要加上
sqlexpress"Data Source=/sqlexpress.;Initial Catalog=数据库;User ID=sa;Password=密码;";
//创建Command对象
public static SqlCommand CreateCommand()
{
//string connectionString = ConfigurationManager.ConnectionStrings["KMProjectDB"].ConnectionString; string connectionString = SGMWConfiguration.ConnectionString;//获得连接字符串
SqlConnection conn = new SqlConnection(connectionString); //通过连接字符串创建连接
SqlCommand comm = conn.CreateCommand(); //通过连接创建Command对象
comm.CommandType = CommandType.StoredProcedure;
return comm;
}
//执行给定的SqlCommand组件
public static DataTable ExecuteSelectCommand(SqlCommand comm)
{
string flag;
DataTable table = null;
try
{
comm.Connection.Open();
SqlDataReader reader = comm.ExecuteReader();
table = new DataTable();
table.Load(reader); //将Reader的数据加载入Datatable中
reader.Close();
}
catch(Exception err) //(Exception ex)
{
flag = err.ToString(); }
finally
{
comm.Connection.Close(); //最后一定关闭连接
}
return table;
}
public static String ExecuteNonQueryCommand(SqlCommand cmd)
{
string flag;
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
return flag="true";
}
catch (Exception err)
{
return flag=err.ToString();
}
finally
{
cmd.Connection.Close();
} }
public static int ExecuteScalar(SqlCommand comm)
{
int count = 0;
try
{
comm.Connection.Open();
count =Convert.ToInt32(comm.ExecuteScalar());
}
catch(Exception err) //(Exception ex)
{
;
}
finally
{
comm.Connection.Close(); //最后一定关闭连接
}
return count;
} public static void BindDropDownList(DataTable table, DropDownList ddl, String textField, String valueField)
{ ddl.DataSource = table;
ddl.DataTextField = textField;
ddl.DataValueField = valueField;
ddl.DataBind();
}
//创建Command对象
public static SqlCommand CreateCommand()
{
//string connectionString = ConfigurationManager.ConnectionStrings["KMProjectDB"].ConnectionString; string connectionString = SGMWConfiguration.ConnectionString;//获得连接字符串
SqlConnection conn = new SqlConnection(connectionString); //通过连接字符串创建连接
SqlCommand comm = conn.CreateCommand(); //通过连接创建Command对象
comm.CommandType = CommandType.StoredProcedure;
return comm;
}
//执行给定的SqlCommand组件
public static DataTable ExecuteSelectCommand(SqlCommand comm)
{
string flag;
DataTable table = null;
try
{
comm.Connection.Open();
SqlDataReader reader = comm.ExecuteReader();
table = new DataTable();
table.Load(reader); //将Reader的数据加载入Datatable中
reader.Close();
}
catch(Exception err) //(Exception ex)
{
flag = err.ToString(); }
finally
{
comm.Connection.Close(); //最后一定关闭连接
}
return table;
}
public static String ExecuteNonQueryCommand(SqlCommand cmd)
{
string flag;
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
return flag="true";
}
catch (Exception err)
{
return flag=err.ToString();
}
finally
{
cmd.Connection.Close();
} }
public static int ExecuteScalar(SqlCommand comm)
{
int count = 0;
try
{
comm.Connection.Open();
count =Convert.ToInt32(comm.ExecuteScalar());
}
catch(Exception err) //(Exception ex)
{
;
}
finally
{
comm.Connection.Close(); //最后一定关闭连接
}
return count;
}
调用的话SqlHelper.SqlDataReader(...);
int i = Sqlhelper.executeNonQuery("INSERT INTO ...", CommandType.Text, null);
i 是影响行数...
插入就写好SQL语句,用SQLHelper里面的函数去调用,传参数等.
就这样,不知道你还想要什么?
public static DataSet executeDataAdapter(string cmdText,CommandType cmdType,SqlParameter[] para) {
using (SqlConnection con=new SqlConnection(sqlcon))
{
try
{
SqlCommand cmd = new SqlCommand();
prepareCommand(con, cmd, null, cmdType, cmdText, para);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch (Exception)
{
throw;
}
}
调用
sqlhelper.executeDataAdapter(,,,,,);
public DataSet executeDataAdapter(string cmdText,CommandType cmdType,SqlParameter[] para) {
using (SqlConnection con=new SqlConnection(sqlcon))
{
try
{
SqlCommand cmd = new SqlCommand();
prepareCommand(con, cmd, null, cmdType, cmdText, para);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
catch (Exception)
{
throw;
}
}
调用
sqlhelper myhelper=new sqlhelper();
myhelper.executeDataAdapt(,,,,,);
服了看动方法的含义,就会调用了
如果要update一个信息
想我写的那个方法,就这样调用sqlhelper.executeNonQuery("update 数据库 set 字段=**,字段=**,字段=** where".commandType.Text,null);其余同理
显示:string connStr = "Data Source=.;Initial Catalog=Car;User ID=sa;Password=;pooling=true"; 1.无参数的
string sqlstr="select * from cartyre"; //根据你自己的实际情况去修改这个取数据的sql语句,当然这条语句中是没有带sql参数的
DataSet ds = SqlHelper.ExecuteDataset(connStr , CommandType.Text, sqlstr); //这个是无参数,返回一个dataset//SqlDataReader dr= SqlHelper.ExecuteReader(connStr , CommandType.Text, sqlstr); //这个是无参数,返回一个DataReade2.有参数的
string sqlstr="select * from cartyre where name=@name";
SqlParameter[] parm = {new SqlParameter("@name", SqlDbType.VarChar, 30)};
parm[0].Value="吉大";
DataSet ds = SqlHelper.ExecuteDataset(connStr , CommandType.Text, sqlstr,parm); //这个是有参数的,返回一个dataset//SqlDataReader dr= SqlHelper.ExecuteReader(connStr , CommandType.Text, sqlstr,parm); //这个是有参数,返回一个DataReade
new SqlParameter("@remains", SqlDbType.Int, 30) };
parm[0].Value="吉大";
parm[1].Value="吉大"; int rowsAffected=SqlHelper.ExecuteNonQuery(connStr,CommandType.Text, sqlstr, parm);
public static SqlCommand CreateCommand()
{
//string connectionString = ConfigurationManager.ConnectionStrings["KMProjectDB"].ConnectionString; string connectionString = SGMWConfiguration.ConnectionString;//获得连接字符串
SqlConnection conn = new SqlConnection(connectionString); //通过连接字符串创建连接
SqlCommand comm = conn.CreateCommand(); //通过连接创建Command对象
comm.CommandType = CommandType.StoredProcedure;
return comm;
}
//执行给定的SqlCommand组件
public static DataTable ExecuteSelectCommand(SqlCommand comm)
{
string flag;
DataTable table = null;
try
{
comm.Connection.Open();
SqlDataReader reader = comm.ExecuteReader();
table = new DataTable();
table.Load(reader); //将Reader的数据加载入Datatable中
reader.Close();
}
catch(Exception err) //(Exception ex)
{
flag = err.ToString(); }
finally
{
comm.Connection.Close(); //最后一定关闭连接
}
return table;
}
public static String ExecuteNonQueryCommand(SqlCommand cmd)
{
string flag;
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
return flag="true";
}
catch (Exception err)
{
return flag=err.ToString();
}
finally
{
cmd.Connection.Close();
} }
public static int ExecuteScalar(SqlCommand comm)
{
int count = 0;
try
{
comm.Connection.Open();
count =Convert.ToInt32(comm.ExecuteScalar());
}
catch(Exception err) //(Exception ex)
{
;
}
finally
{
comm.Connection.Close(); //最后一定关闭连接
}
return count;
} public static void BindDropDownList(DataTable table, DropDownList ddl, String textField, String valueField)
{ ddl.DataSource = table;
ddl.DataTextField = textField;
ddl.DataValueField = valueField;
ddl.DataBind();
}
构造命令SqlCommand cmd = new SqlCommand("命令",con);
执行命令的过程 cmd.执行命令针对不同的语句有适当的方法.看一下就好了
http://topic.csdn.net/u/20080925/17/647c91c7-bfd6-402a-b6ba-409d8a4d8881.html
我发了一系列,你可以看。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms; using System.Data.SqlClient; namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} string connStr = "Data Source=.;Initial Catalog=Car;User ID=sa;Password=;pooling=true";
//……
string strSql="select * from table1";
DataSet ds = SqlHelper.ExecuteDataset(connStr, CommandType.Text, strSql.ToString());
//下面对DataSet的操作
}
}
很快上手的。