Question

visual studio数据库连接除了用控件连接外,用代码实行数据连接怎么做?

visual studio数据库连接除了用控件连接外,用代码实行数据连接怎么做?asp.net中sql语句与asp中的sql语法有改变吗?看了很多有关的书都不知道怎么在asp.net网页中实行sql数据查询(除了使用控件外),数据绑定和数据库连接有什么区别啊。

Answer 1

c#版本

using System;
using System.Data;
using System.Data.SqlClient;

namespace Haha
{
/// <summary>
/// DBM 的摘要说明。
/// </summary>
public class DBM
{
public bool isSingle=true;
private SqlConnection conn;
public DBM()
{
conn=new SqlConnection("server=.;uid=sa;database=pubs");
}
public SqlConnection getConn()
{
return conn;
}

public DataTable getTable(string sql)
{
DBM dbm=new DBM();
SqlDataAdapter dap=new SqlDataAdapter(sql,dbm.getConn());
DataTable dt=new DataTable();
dap.Fill(dt);
return dt;
}

public DataSet getDataSet(string sql)
{
DBM dbm=new DBM();
SqlDataAdapter dap=new SqlDataAdapter(sql,dbm.getConn());
DataSet ds=new DataSet();
dap.Fill(ds);
return ds;
}

public DataTable getTable(string sql,int start,int size)
{
DBM dbm=new DBM();
SqlDataAdapter dap=new SqlDataAdapter(sql,dbm.getConn());
DataSet ds=new DataSet();
dap.Fill(ds,start,size,"tmp");
return ds.Tables["tmp"];
}
public Exception Execute(string sql)
{
DBM dbm=new DBM();
SqlCommand cmd=new SqlCommand(sql,dbm.getConn());
cmd.Connection.Open();
SqlTransaction tran=cmd.Connection.BeginTransaction();
cmd.Transaction=tran;
Exception exret=null;
try
{
cmd.ExecuteNonQuery();
tran.Commit();
}
catch(Exception ex)
{
tran.Rollback();
exret=ex;
}
finally
{
cmd.Connection.Close();
}
return exret;
}

public Exception CallProc(MakeParameter mp)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = mp.ProcName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = getConn();

for (int i = 0; i < mp.List.Count; i++)
{
cmd.Parameters.Add(mp.List[i]);
}
foreach(object key in mp.Table.Keys)
{
cmd.Parameters.Add(mp.Table[key]);
}

cmd.Connection.Open();
SqlTransaction tran = cmd.Connection.BeginTransaction();
cmd.Transaction = tran;
try
{
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
return ex;
}
finally
{
cmd.Connection.Close();
}

return null;
}

public DataSet CallProcWithDataSet(MakeParameter mp)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = mp.ProcName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = getConn();

for (int i = 0; i < mp.List.Count; i++)
{
cmd.Parameters.Add(mp.List[i]);
}
foreach (object key in mp.Table.Keys)
{
cmd.Parameters.Add(mp.Table[key]);
}

SqlDataAdapter dap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();

try
{

dap.Fill(ds);

}
catch
{

}
finally
{
if(cmd.Connection.State!=ConnectionState.Closed)
cmd.Connection.Close();
}

return ds;
}

}
}

Answer 2

DataSet myDataSet = new DataSet();
string sConnect;
sConnect = "Data Source=CUTEMITE;Initial Catalog="Student Information";Integrated Security=True";
SQL.SqlConnection dbConn = new SQL.SqlConnection(sConnect);

try
{
SQL.SqlCommand dbCmd;
dbCmd = new SQL.SqlCommand(qurey,dbConn);

SQL.SqlDataAdapter dbAdapter;
dbAdapter = new SQL.SqlDataAdapter(dbCmd);

dbConn.Open();
dbAdapter.Fill(myDataSet);
dbConn.Close();
}
catch (Exception ex)
{
throw ex;

}
finally
{
dbConn.Close();
}

运行的SQL.SqlConnection dbConn = new SQL.SqlConnection(sConnect);时候出错

这一行
"Data Source=CUTEMITE;Initial Catalog="Student Information";Integrated Security=True";
是从app.config里面的connectstring拷贝过来的

Answer 3

Dim AdoSqlText As String = "data source=" & sDataAddress & ";initial catalog=" & sDataName & ";user id=" & sDataUserID & ";password=" & sDataPassword & ";Pooling=true;Min Pool Size = 10"

Dim Conn As New SqlConnection(AdoSqlText)
Conn.Open()
'
Dim Cmd As New SqlCommand("SQL语句", Conn)
Cmd.ExecuteNonQuery()
'
Cmd.Cancel()
Cmd.Dispose()
Conn.Close()