给个例子,呵呵
string connStr="server=localhost;uid=sa;pwd=sa;database=test";
SqlConnection objConn=new SqlConnection(connStr);
DataSet ds=new DataSet();
string sqlstr="select * from testtable where 2>1";
SqlDataAdapter myAdapter=new SqlDataAdapter(sqlstr,objConn);
myAdapter.Fill(ds,"testtable");
解决方案 »
- 求助,如何实现类似在csdn论坛中,当鼠标放在头向上的时候,动态的显示相关信息
- vs2003调试程序时 要求输入windowsNT用户名和密码
- asp.net动态生成的控件TextBox取值问题,用一个按钮(非自动生成)怎么取得TextBox里面的值
- div 层的显示 asp.net
- WORD文件,上传到服务器,怎样显示在WEB页面中?
- ServerXMLHTTP问题
- 打印问题:如何打印固定格式的报表?
- 请帮介绍好的国内的免费的空间支持asp.net和Mssql
- 急招“高级Java工程师”
- 在textbox中,如果在控件失去焦点时,判断里面的数据是不是符合我的要求,这个应该怎么实现
- 请问华东有没有比较实力的asp.net网站开发商
- Application目录必须是一个虚拟目录吗?
比如,一个执行sql语句的函数,我这样定义的(用的是oracle数据库)
//========================================================================================
//功能 : 执行指定的SQL语句
//参数 : 要执行的Sql语句
//返回值 : 1:执行成功;0:执行失败
//创建日期: 2002-12-23
//========================================================================================
public int ExeSql(string Sql)
{
OracleConnection myConnection = new OracleConnection(this.ConnectionString);
OracleCommand myCommand=new OracleCommand(Sql,myConnection);
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
myCommand.Dispose();
myConnection.Close();
return 1;
}
catch(OracleException e)
{
errMsg = "";
errMsg += e.Message;
myConnection.Close();
return 0;
}
}
but that is not always true, when you consider efficiency is the primary factor,I prefer stored procedure.
那我们这样的办法也算很正规的方法喽?
有没qq?能联系你吗?
比如可以写一个Database类:
using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace TomLi.Components
{
/// <summary>
/// ADO.NET data access using the SQL Server Managed Provider.
/// </summary>
public class Database : IDisposable
{
// connection to data source
private SqlConnection con;
/// <summary>
/// Run stored procedure.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <returns>Stored procedure return value.</returns>
public int RunProc(string procName)
{
SqlCommand cmd = CreateCommand(procName, null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
} /// <summary>
/// Run stored procedure.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="prams">Stored procedure params.</param>
/// <returns>Stored procedure return value.</returns>
public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
} /// <summary>
/// Run stored procedure.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="dataReader">Return result of procedure.</param>
public void RunProc(string procName, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procName, null);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
} /// <summary>
/// Run stored procedure.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="prams">Stored procedure params.</param>
/// <param name="dataReader">Return result of procedure.</param>
public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procName, prams);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
/// <summary>
/// Create command object used to call stored procedure.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="prams">Params to stored procedure.</param>
/// <returns>Command object.</returns>
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
// make sure connection is open
Open(); //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure; // add proc parameters
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// return param
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null)); return cmd;
} /// <summary>
/// Open the connection.
/// </summary>
private void Open()
{
// open connection
if (con == null)
{
con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
con.Open();
}
} /// <summary>
/// Close the connection.
/// </summary>
public void Close()
{
if (con != null)
con.Close();
} /// <summary>
/// Release resources.
/// </summary>
public void Dispose()
{
// make sure connection is closed
if (con != null)
{
con.Dispose();
con = null;
}
} /// <summary>
/// Make input param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
} /// <summary>
/// Make input param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <returns>New parameter.</returns>
public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
} /// <summary>
/// Make stored procedure param.
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Direction">Parm direction.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param; if(Size > 0)
param = new SqlParameter(ParamName, DbType, Size);
else
param = new SqlParameter(ParamName, DbType); param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value; return param;
}
}
}
我的感觉存储过程要用到恰到好处,简单的功能就没必要了!
十分复杂的功能就用存储过程,因为用sql语句就不划算了!
只需创建过程一次并将其存储在数据库中,以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建,并可独立于程序源代码而单独修改。允许更快执行。
如果某操作需要大量 Transact-SQL 代码或需重复执行,存储过程将比 Transact-SQL 批代码的执行要快。将在创建存储过程时对其进行分析和优化,并可在首次执行该过程后使用该过程的内存中版本。每次运行 Transact-SQL 语句时,都要从客户端重复发送,并且在 SQL Server 每次执行这些语句时,都要对其进行编译和优化。减少网络流量。
一个需要数百行 Transact-SQL 代码的操作由一条执行过程代码的单独语句就可实现,而不需要在网络中发送数百行代码。可作为安全机制使用。
即使对于没有直接执行存储过程中语句的权限的用户,也可授予他们执行该存储过程的权限。
现在正在研究ing!!!
如有需要petshop,请跟我联系!!