如何将数据库查询结果放入变量中
如:selectCOUNT(*)fromPHOTO_PHOTOSgroupbyalbumid;我用的是SQLSERVER2005数据库,我想把这个结果放入一个变量中可以吗...
如:select COUNT(*) from PHOTO_PHOTOS group by albumid;
我用的是SQLSERVER2005数据库,我想把这个结果放入一个变量中可以吗? 展开
我用的是SQLSERVER2005数据库,我想把这个结果放入一个变量中可以吗? 展开
6个回答
展开全部
在C#程序里面,使用SQL语句的一般方法是:
1、建立连接:OleDbConnection或者SqlConnection
2、建立SQL命令:OleDbCommand或者SqlCommand
3、执行命令。
执行命令的方法有很多:
a、ExecuteScalar();//返回的是结果的第一行第一列,
b、使用DataTable,返回一个表,
c、Reader()//向前只读
返回的结果使用类型转换赋值给你的变量。
下面是一个例子,连接的Access数据库,要改成SQL Server只需要改连接常量就行了。
namespace AccessControl
{
public class AccessControl
{
/// <summary>
/// 连接常量
/// </summary>
private OleDbConnection Con;
/// <summary>
/// SQL命令
/// </summary>
private OleDbCommand Com;
/// <summary>
/// 初始化一个连接实例
/// </summary>
/// <param name="FilePath">文件名</param>
public AccessControl(string FilePath)
{
Con = new OleDbConnection(@"Provider =Microsoft.Jet.OLEDB.4.0;Data Source =" + @FilePath);
Com = new OleDbCommand();
}
/// <summary>
/// 返回执行所受影响的函数
/// </summary>
/// <param name="com"></param>
/// <returns></returns>
public int ExecuteGetLines(string com)
{
try
{
Com.CommandText = com;
Com.Connection = Con;
Con.Open();
int i = 0;
i = Com.ExecuteNonQuery();
return i;
}
catch(Exception e)
{
throw new System.ArgumentException("错误",e.Message);
return 0;
}
finally
{
CloseCon();
}
}
/// <summary>
/// 返回结果的第一行第一列
/// </summary>
/// <param name="com"></param>
/// <returns></returns>
public object ExecuteGetCol(string com)
{
try
{
Com.CommandText = com;
Com.Connection = Con;
Con.Open();
object o = null;
o = (object)Com.ExecuteScalar();
return o;
}
catch (Exception e)
{
throw new System.ArgumentException("错误", e.Message);
}
finally
{
CloseCon();
}
}
/// <summary>
/// 返回一个数据集合DataTable
/// </summary>
/// <param name="com"></param>
/// <returns></returns>
public DataTable ExecuteGetTable(string com)
{
try
{
DataTable dt = new DataTable();
Com.CommandText = com;
Com.Connection = Con;
OleDbDataAdapter dad = new OleDbDataAdapter(Com);
dad.Fill(dt);
return dt;
}
catch(Exception e)
{
throw new System.ArgumentException("错误", e.Message);
}
finally
{
CloseCon();
}
}
/// <summary>
/// 关闭连接
/// </summary>
public void CloseCon()
{
Com.Dispose();
Con.Close();
}
}
}
1、建立连接:OleDbConnection或者SqlConnection
2、建立SQL命令:OleDbCommand或者SqlCommand
3、执行命令。
执行命令的方法有很多:
a、ExecuteScalar();//返回的是结果的第一行第一列,
b、使用DataTable,返回一个表,
c、Reader()//向前只读
返回的结果使用类型转换赋值给你的变量。
下面是一个例子,连接的Access数据库,要改成SQL Server只需要改连接常量就行了。
namespace AccessControl
{
public class AccessControl
{
/// <summary>
/// 连接常量
/// </summary>
private OleDbConnection Con;
/// <summary>
/// SQL命令
/// </summary>
private OleDbCommand Com;
/// <summary>
/// 初始化一个连接实例
/// </summary>
/// <param name="FilePath">文件名</param>
public AccessControl(string FilePath)
{
Con = new OleDbConnection(@"Provider =Microsoft.Jet.OLEDB.4.0;Data Source =" + @FilePath);
Com = new OleDbCommand();
}
/// <summary>
/// 返回执行所受影响的函数
/// </summary>
/// <param name="com"></param>
/// <returns></returns>
public int ExecuteGetLines(string com)
{
try
{
Com.CommandText = com;
Com.Connection = Con;
Con.Open();
int i = 0;
i = Com.ExecuteNonQuery();
return i;
}
catch(Exception e)
{
throw new System.ArgumentException("错误",e.Message);
return 0;
}
finally
{
CloseCon();
}
}
/// <summary>
/// 返回结果的第一行第一列
/// </summary>
/// <param name="com"></param>
/// <returns></returns>
public object ExecuteGetCol(string com)
{
try
{
Com.CommandText = com;
Com.Connection = Con;
Con.Open();
object o = null;
o = (object)Com.ExecuteScalar();
return o;
}
catch (Exception e)
{
throw new System.ArgumentException("错误", e.Message);
}
finally
{
CloseCon();
}
}
/// <summary>
/// 返回一个数据集合DataTable
/// </summary>
/// <param name="com"></param>
/// <returns></returns>
public DataTable ExecuteGetTable(string com)
{
try
{
DataTable dt = new DataTable();
Com.CommandText = com;
Com.Connection = Con;
OleDbDataAdapter dad = new OleDbDataAdapter(Com);
dad.Fill(dt);
return dt;
}
catch(Exception e)
{
throw new System.ArgumentException("错误", e.Message);
}
finally
{
CloseCon();
}
}
/// <summary>
/// 关闭连接
/// </summary>
public void CloseCon()
{
Com.Dispose();
Con.Close();
}
}
}
展开全部
昏.这样情况下不要用ExecuteReader(),那样多麻烦.而且推荐用 SqlClient.
SqlConnection conn = new SqlConnection("//数据库连接字符串信息//");
SqlCommand cmd = new SqlCommand("select COUNT(*) from PHOTO_PHOTOS group by albumid", conn);
conn.Open();
int totalCount = (int)cmd.ExecuteScalar(); //totalcount 就是总数
conn.Close();
SqlConnection conn = new SqlConnection("//数据库连接字符串信息//");
SqlCommand cmd = new SqlCommand("select COUNT(*) from PHOTO_PHOTOS group by albumid", conn);
conn.Open();
int totalCount = (int)cmd.ExecuteScalar(); //totalcount 就是总数
conn.Close();
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
我举个简单的例子:
int count=0;
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "select COUNT(*) from PHOTO_PHOTOS group by albumid; ";
try
{
OleDbDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
count=reader.GetInt32(0);//获取统计数
}
reader.close();//关闭记录集
}
catch(Exception e)
{
//异常信息
}
基本思路就这样
int count=0;
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "select COUNT(*) from PHOTO_PHOTOS group by albumid; ";
try
{
OleDbDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
count=reader.GetInt32(0);//获取统计数
}
reader.close();//关闭记录集
}
catch(Exception e)
{
//异常信息
}
基本思路就这样
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
private int CalculateRecord()
{
try
{
int recordCount;
SqlConnection oc = new SqlConnection(connectionstring);
oc.Open();
string OleDb = "select count(*) as [count] from News where TypeName='文教视点'";
SqlCommand cmd = new SqlCommand(OleDb, oc);
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.Read())
{
recordCount = Int32.Parse(sdr["count"].ToString());
}
else
{
recordCount = 0;
}
sdr.Close();
oc.Close();
return recordCount;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
{
try
{
int recordCount;
SqlConnection oc = new SqlConnection(connectionstring);
oc.Open();
string OleDb = "select count(*) as [count] from News where TypeName='文教视点'";
SqlCommand cmd = new SqlCommand(OleDb, oc);
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.Read())
{
recordCount = Int32.Parse(sdr["count"].ToString());
}
else
{
recordCount = 0;
}
sdr.Close();
oc.Close();
return recordCount;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
本回答被提问者采纳
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
ExecuteScalar().ToString()
用变量接受就可以
用变量接受就可以
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询