CREATE PROCEDURE PNoteID
@id char(16)
AS
select *
from VAllNote
where id= @id
GO这个是一个通过ID从视图检索的存储过程(一眼就知道了,不多说了)我是C#菜鸟,不知道在C#里面应该如何调用,并且获取结果
我想要一个一个字段的获取值
如果查询没有记录,又应该在程序里如何判断?或者还是修改一下存储过程?
希望大家能多多帮助,附上源码
@id char(16)
AS
select *
from VAllNote
where id= @id
GO这个是一个通过ID从视图检索的存储过程(一眼就知道了,不多说了)我是C#菜鸟,不知道在C#里面应该如何调用,并且获取结果
我想要一个一个字段的获取值
如果查询没有记录,又应该在程序里如何判断?或者还是修改一下存储过程?
希望大家能多多帮助,附上源码
解决方案 »
- 求帮助 winform
- 用C#写一个xml文件处理工具,求指教
- datagridview中的combobox 如何实现联动
- winform 的 flowLayoutPanel控件 如何让滚动条在最后边
- gridview中对应数据库bit字段怎么读取
- label的text为什么无法更新
- debug文件中出现了一个ar的文件夹,咋回事
- 求救:为什么我的应用程序不能添加已注册的COM组件的引用?(内详)
- "" == key 比 key=="" 好哪?
- 安装.net2003没有出错,为什么进入IDE马上出现“WIndows INSTALL”对话框然后退出?????
- 探讨:关于C#把数据导出到Excel的问题?
- 如何使panel控件透明并显示窗体image??
System.Data.OleDb.OleDbCommand cm = new System.Data.OleDb.OleDbCommand("存储过程名", cnn);
cm.CommandType = System.Data.CommandType.StoredProcedure; //填充到DataTable
DataTable dt = new DataTable();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(cm);
da.Fill(dt);
if (dt.Rows.Count == 0)
{
//没有数据
}
else
{
string a = dt.Rows[0][0].ToString();//第一行第一列的值
}
System.Data.OleDb.OleDbCommand cm = new System.Data.OleDb.OleDbCommand("存储过程名", cnn);
cm.CommandType = System.Data.CommandType.StoredProcedure;
//使用DataReader
System.Data.OleDb.OleDbDataReader reader = cm.ExecuteReader();
if (reader.Read())
{
string a = reader[0].ToString();//第一行第一列的值
}
else
{
//没有数据
}
System.Data.OleDb.OleDbCommand cm = new System.Data.OleDb.OleDbCommand("存储过程名", cnn);
cm.CommandType = System.Data.CommandType.StoredProcedure;
//使用DataReader
System.Data.OleDb.OleDbDataReader reader = cm.ExecuteReader();
string tmp = reader.HasRows;//这也可以判断是否有数据
我用MS SQL的数据库也是这样?
回答的很全面了不知道下面这个怎么判断?
string tmp = reader.HasRows;//这也可以判断是否有数据
可以使用SQLCOMMAND对象,其TYPE类型为
StoredProcedure
{
//ASP.NET 2.0
//你想要的是这意思吗?
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand sc = new SqlCommand();
sc.CommandText = "存储过程1";
sc.CommandType = CommandType.StoredProcedure;
sc.Connection = conn;
sda.SelectCommand = sc;
DataSet ds = new DataSet();
sda.Fill(ds, "Table1"); conn.Close();
return ds;
}
{
//ASP.NET 2.0
//你想要的是这意思吗?
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand sc = new SqlCommand();
sc.CommandText = "存储过程1";
sc.CommandType = CommandType.StoredProcedure;
sc.Connection = conn;
sda.SelectCommand = sc;
DataSet ds = new DataSet();
sda.Fill(ds, "Table1"); sc = new SqlCommand();
sc.CommandText = "存储过程2";
sc.CommandType = CommandType.StoredProcedure;
sc.Connection = conn;
sda.SelectCommand = sc;
sda.Fill(ds, "Table2"); sc = new SqlCommand();
sc.CommandText = "存储过程3";
sc.CommandType = CommandType.StoredProcedure;
sc.Connection = conn;
sda.SelectCommand = sc;
try
{
conn.Open();
sda.Fill(ds, "Table3"); conn.Close();
}
catch{}
return ds;
}
{
//ASP.NET 2.0
//你想要的是这意思吗?
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
SqlConnection conn = new SqlConnection(connString);
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand sc = new SqlCommand();
sc.CommandText = "存储过程1";
sc.CommandType = CommandType.StoredProcedure;
sc.Connection = conn;
sda.SelectCommand = sc;
DataSet ds = new DataSet();
try
{
conn.Open();
sda.Fill(ds, "Table1"); conn.Close();
}
catch{}
return ds;
}
你三个例子 哪个写存储过程参数了, 第一个 根本就不行,2,3还可以补上还有人说正解
服了
using System.Data;
using System.Data.SqlClient;private SqlConnection sqlcon=new SqlConnection("uid=?;pwd=?;data source=.;database=?;");
//添入相应的用户名和密码 还有你要连接的数据库
private DataSet ds=new DataSet();
private SqlDataReader sDR;
private DataTable dt=new DataTable();public int OpenCon()
{
try
{
if(this.sqlcon.State==System.Data.ConnectionState.Open)
this.sqlcon.Close();
this.sqlcon.Open();
}
catch(Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
return -1;
}
return 0;
}
public int CloseCon()
{
try
{
if(this.sqlcon.State==System.Data.ConnectionState.Closed)
this.sqlcon.Open();
this.sqlcon.Close();
}
catch(Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
return -1;
}
return 0;
}private void FillVAllNote()
{
DataColumn dc1 = new DataColumn("id",typeof(string));
DataColumn dc2 = new DataColumn("name",typeof(string));
DataColumn dc3 = new DataColumn("sex",typeof(string));
this.sDt.Columns.Clear();
this.sDt.Columns.Add(dc1);
this.sDt.Columns.Add(dc2);
this.sDt.Columns.Add(dc3);
} this.OpenCon();
SqlCommand sqlCmd=new SqlCommand(存储过程的名字,this.sqlcon);
sqlCmd.CommandType=CommandType.StoredProcedure;
SqlParameter pid =sqlCmd.Parameters.Add("@id",SqlDbType.Char,16);
pid.Value=需要查询的ID值;
if(sqlCmd.ExecuteNonQuery()!=0)
{
this.sDR=sqlCmd.ExecuteReader();
this.FillVAllNote();
this.sDt.Rows.Clear();
while(this.sSDR.Read())
{
DataRow dr = this.sDt.NewRow();
dr[0]=this.sSDR.GetValue(0);
dr[1]=this.sSDR.GetValue(1);
dr[2]=this.sSDR.GetValue(2);
dr[3]=this.sSDR.GetValue(3);
dr[4]=this.sSDR.GetValue(4);
dr[5]=this.sSDR.GetValue(5);
dr[6]=this.sSDR.GetValue(6);
this.sDt.Rows.Add(dr);
}
}
con.CloseCon();
this.sProc="";
return Convert.ToInt32(pResult.Value);
下面这个是正确的
假设你的VAllNote 视图有id,name,sex 三个字段using System;
using System.Data;
using System.Data.SqlClient;private SqlConnection sqlcon=new SqlConnection("uid=?;pwd=?;data source=.;database=?;");
//添入相应的用户名和密码 还有你要连接的数据库
private DataSet ds=new DataSet();
private SqlDataReader sDR;
private DataTable dt=new DataTable();public int OpenCon()
{
try
{
if(this.sqlcon.State==System.Data.ConnectionState.Open)
this.sqlcon.Close();
this.sqlcon.Open();
}
catch(Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
return -1;
}
return 0;
}
public int CloseCon()
{
try
{
if(this.sqlcon.State==System.Data.ConnectionState.Closed)
this.sqlcon.Open();
this.sqlcon.Close();
}
catch(Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
return -1;
}
return 0;
}private void FillVAllNote()
{
DataColumn dc1 = new DataColumn("id",typeof(string));
DataColumn dc2 = new DataColumn("name",typeof(string));
DataColumn dc3 = new DataColumn("sex",typeof(string));
this.dt.Columns.Clear();
this.dt.Columns.Add(dc1);
this.dt.Columns.Add(dc2);
this.dt.Columns.Add(dc3);
} this.OpenCon();
SqlCommand sqlCmd=new SqlCommand(存储过程的名字,this.sqlcon);
sqlCmd.CommandType=CommandType.StoredProcedure;
SqlParameter pid =sqlCmd.Parameters.Add("@id",SqlDbType.Char,16);
pid.Value=需要查询的ID值;
if(sqlCmd.ExecuteNonQuery()!=0)
{
this.sDR=sqlCmd.ExecuteReader();
this.FillVAllNote();
this.dt.Rows.Clear();
while(this.sDR.Read())
{
DataRow dr = this.sDt.NewRow();
dr[0]=this.sDR.GetValue(0);
dr[1]=this.sDR.GetValue(1);
dr[2]=this.sDR.GetValue(2);
this.dt.Rows.Add(dr);
}
}
else
{
//无记录
}
this.CloseCon();
sc.CommandText = "存储过程1";
sc.CommandType = CommandType.StoredProcedure;
Sandy945(阿非):
拜托,三个星你好好看看,人家问的是 传入一个ID 根据ID查询 结果,
{
/// <summary>
/// 具有写权限的连接语句
/// </summary>
protected System.Data.SqlClient.SqlConnection writecon;
/// <summary>
/// 只具有读权限的连接语句
/// </summary>
protected SqlConnection readcon;
/// <summary>
/// 执行的命令
/// </summary>
protected System.Data.SqlClient.SqlCommand sqlcmd;
/// <summary>
/// 初始化连接语句
/// </summary>
/// <param name="both">即可读也可写的连接语句</param>
public DbObject(string both):this(both,both)
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 初始化连接语句
/// </summary>
/// <param name="read">只可读的连接</param>
/// <param name="both">即可读也可写的连接</param>
public DbObject(string read,string write)
{
this.readcon=new SqlConnection(read);
this.writecon=new SqlConnection(write);
this.sqlcmd=new SqlCommand();
}
public string ReClearString(string s)
{
return this.reClearString(s);
}
public System.Data.SqlClient.SqlDataReader GetReader(string cmd)
{
return this.getReader(cmd);
}
public System.Data.SqlClient.SqlDataReader GetReader(string procedure,string []p,string[]s)
{
return this.getReader(procedure,p,s);
}
public int GetUpdateResult(string cmd)
{
return this.getUpdateResult(cmd);
}
protected virtual int GetUpdateResult(string procedure,string []p,string[]s)
{
return this.getUpdateResult(procedure,p,s);
}
public DataSet GetDataSet(string cmd)
{
return this.getDataSet(cmd);
}
public DataSet GetDataSet(string procedure,string []p,string[]s)
{
return this.getDataSet(procedure,p,s);
}
public DataSet GetDataSet(string procedure,string[]p,string[]s,int nStart,int MaxCount)
{
return this.getDataSet(procedure,p,s,nStart,MaxCount);
}
public object GetScalar(string cmd)
{
return this.getScalar(cmd);
}
public object GetScalar(string procedure,string []p,string[]s)
{
return this.getScalar(procedure,p,s);
}
protected virtual System.Data.SqlClient.SqlDataReader getReader(string cmd)
{
SqlDataReader r=null;
try
{
this.sqlcmd.Connection=this.readcon;
this.sqlcmd.CommandText=cmd;
this.readcon.Open();
r=this.sqlcmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch(Exception error)
{
Console.WriteLine(error.Message);
r=null;
}
return r;
}
protected virtual System.Data.SqlClient.SqlDataReader getReader(string procedure,string []p,string[]s)
{
SqlDataReader r=null;
try
{
if(!this.giveCmdValue(this.readcon,procedure,p,s))
return null;
this.sqlcmd.Connection.Open();
r=this.sqlcmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch(Exception error)
{
Console.WriteLine(error.Message);
r=null;
}
return r;
}
protected virtual int getUpdateResult(string cmd)
{
int r=0;
try
{
this.sqlcmd.Connection=this.writecon;
this.sqlcmd.CommandText=cmd;
r=this.sqlcmd.ExecuteNonQuery();
}
catch(Exception error)
{
Console.WriteLine(error.Message);
r=0;
}
finally
{
this.sqlcmd.Connection.Close();
}
return r;
}
protected virtual int getUpdateResult(string procedure,string []p,string[]s)
{
int r=0;
try
{
if(!this.giveCmdValue(this.writecon,procedure,p,s))
return 0;
this.sqlcmd.Connection.Open();
r=this.sqlcmd.ExecuteNonQuery();
}
catch(Exception error)
{
Console.WriteLine(error.Message);
r=0;
}
finally
{
this.sqlcmd.Connection.Close();
}
return r;
}
protected virtual DataSet getDataSet(string cmd)
{
DataSet ds=new DataSet();
try
{
this.sqlcmd.Connection=this.readcon;
this.sqlcmd.CommandText=cmd; this.readcon.Open(); System.Data.SqlClient.SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=this.sqlcmd; da.Fill(ds);
}
catch(Exception error)
{
Console.WriteLine(error.Message);
ds=null;
}
finally
{
this.sqlcmd.Connection.Close();
}
return ds;
} protected virtual DataSet getDataSet(string procedure,string []p,string[]s)
{
DataSet ds=new DataSet();
try
{
if(!this.giveCmdValue(this.readcon,procedure,p,s))
return null;
System.Data.SqlClient.SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=this.sqlcmd;
this.sqlcmd.Connection.Open();
da.Fill(ds);
}
catch(Exception error)
{
Console.WriteLine(error.Message);
ds=null;
}
finally
{
this.sqlcmd.Connection.Close();
}
return ds;
}
protected virtual DataSet getDataSet(string procedure,string[]p,string[]s,int nStart,int MaxCount)
{
DataSet ds=new DataSet();
try
{
if(!this.giveCmdValue(this.readcon,procedure,p,s))
return null;
System.Data.SqlClient.SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=this.sqlcmd;
this.sqlcmd.Connection.Open();
da.Fill(ds,nStart,MaxCount,"table");
}
catch(Exception error)
{
Console.WriteLine(error.Message);
ds=null;
}
finally
{
this.sqlcmd.Connection.Close();
}
return ds;
} protected virtual object getScalar(string cmd)
{
object r;
try
{
this.sqlcmd.Connection=this.readcon;
this.sqlcmd.CommandText=cmd;
this.sqlcmd.Connection.Open();
r=this.sqlcmd.ExecuteScalar();
}
catch(Exception error)
{
Console.WriteLine(error.Message);
r=null;
}
finally
{
this.sqlcmd.Connection.Close();
}
return r;
} protected virtual object getScalar(string procedure,string []p,string[]s)
{
object r;
if(!this.giveCmdValue(this.readcon,procedure,p,s))
return null;
try
{
this.sqlcmd.Connection.Open();
r=this.sqlcmd.ExecuteScalar();
}
catch(Exception error)
{
Console.WriteLine(error.Message);
r=null;
}
finally
{
this.sqlcmd.Connection.Close();
}
return r;
}
/// <summary>
/// 给command赋值
/// </summary>
/// <param name="sqlcon">连接语句</param>
/// <param name="procedure">存储过程</param>
/// <param name="p">参数名称</param>
/// <param name="s">参数值</param>
/// <returns>是否成功赋值</returns>
protected virtual bool giveCmdValue(System.Data.SqlClient.SqlConnection sqlcon,string procedure,string []p,string[]s)
{
this.sqlcmd.Connection=sqlcon;
this.sqlcmd.CommandText=procedure;
this.sqlcmd.CommandType=CommandType.StoredProcedure;
if(s!=null)
{
if(s.Length!=p.Length)
{
return false;
}
for(int i=0;i<s.Length;i++)
{
this.sqlcmd.Parameters.Add(p[i],(object)(this.clearString(s[i])));
}
}
return true;
} protected virtual string clearString(string s)
{
if(s==null)
return null;
s=s.Replace("'",""");
s=s.Replace(">",">");
s=s.Replace("<","<");
return s;
}
protected virtual string reClearString(string s)
{
if(s==null)
return null;
s=s.Replace(""","'");
s=s.Replace(">",">");
s=s.Replace("<","<");
return s;
}