//: SqlObject.cs //: 数据库访问模块 - 底层 - Sqlnamespace Skyiv { using System.Data; using System.Data.SqlClient; class SqlObject { protected SqlConnection conn; // 构造函数,参数为连接串。 protected SqlObject(string strConn) { conn = new SqlConnection(strConn); } // 执行SQL,将结果返回到数据集中。 public DataSet DsQuery(string strSql, params SqlParameter [] paras) { SqlCommand cmd = new SqlCommand(strSql, conn); foreach (SqlParameter para in paras) { cmd.Parameters.Add(para); } SqlDataAdapter apt = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); apt.Fill(ds); return ds; } // 执行SQL,将结果返回到数据表中。 public DataTable DtQuery(string strSql, params SqlParameter [] paras) { return DsQuery(strSql, paras).Tables[0]; } // 执行SQL,将结果返回到数据行中。 public DataRow DrQuery(string strSql, params SqlParameter [] paras) { DataTable dt = DsQuery(strSql, paras).Tables[0]; if (dt.Rows.Count == 0) return null; else return dt.Rows[0]; } // 执行SQL,并返回查询所返回的结果集中第一行的第一列。 public object ScalarQuery(string strSql, params SqlParameter [] paras) { SqlCommand cmd = new SqlCommand(strSql, conn); try { cmd.Connection.Open(); foreach (SqlParameter para in paras) { cmd.Parameters.Add(para); } return cmd.ExecuteScalar(); } finally { cmd.Connection.Close(); } } // 执行SQL,返回受影响的行数。 public int ExecNonQuery(string strSql, params SqlParameter [] paras) { SqlCommand cmd = new SqlCommand(strSql, conn); cmd.Connection.Open(); foreach (SqlParameter para in paras) { cmd.Parameters.Add(para); } int n = cmd.ExecuteNonQuery(); cmd.Connection.Close(); return n; } // 执行SQLs,返回受影响的行数。 public int ExecNonQuerys(params string [] strSqls) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.Connection.Open(); int n = 0; foreach (string strSql in strSqls) { cmd.CommandText = strSql; n += cmd.ExecuteNonQuery(); } cmd.Connection.Close(); return n; } } // End of class SqlObject } // End of namespace Skyiv
//: SqlQuery.cs //: 数据库访问模块 - 中间层 - Sqlnamespace Skyiv { using System; using System.Data; using System.Collections; using System.Data.SqlClient; class SqlQuery : SqlObject { static string strConn = System.Configuration.ConfigurationSettings.AppSettings["SqlDSN"]; // 构造函数 public SqlQuery() : base(strConn) {} // 查询系统参数(营业日期、营业状态) public DataRow DrSystemArgs() { string strSql = "SELECT * FROM [SystemArgs]"; return DrQuery(strSql); } // 查询营业员 public DataTable DtOperator() { string strSql = "SELECT * FROM [operator] ORDER BY [name]"; return DtQuery(strSql); } // 计算营业点的用户数(Attribute == '0') public int UserNumAttr0() { object o = ScalarQuery("select count(*) from [user] where [attribute]='0'"); if (o == null) return 0; return (int)o; } } // End of class SqlQuery } // End of namespace Skyiv
string connectionString = connectionString; string commandString = "select name from dbo.sysobjects where xtype = 'U' and status >0"; SqlDataAdapter ada = new SqlDataAdapter(commandString, connectionString); DataSet ds = new DataSet(); ada.Fill(ds); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { listBox1.Items.Add(ds.Tables[0].Rows[i][0]); }
贴个简单点的access的 string strDBPath = "";//数据库路径 string strConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strDBPath ;//其他数据库改连接字即可 OleDbConnection m_Conn = new OleDbConnection(strConnStr); m_Conn.Open(); string strSQL = "SELECT ****";//相应sql语句 comm = m_Conn.CreateCommand(); comm.CommandText = strSQL; reader = comm.ExecuteReader();//或者comm.ExecuteNonQuery(); if (reader.Read()) {//以下省略 }
//运用参数
cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = "M%";
SqlDataReader reader;
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
Employee employee = new Employee();
employee.EmployeeID = reader.GetInt32(0);
employee.FirstName = reader.GetString(1);
employee.LastName = reader[2].ToString();
employeeList.Add(employee);
}
reader.Close();
//: 数据库访问模块 - 底层 - Sqlnamespace Skyiv
{
using System.Data;
using System.Data.SqlClient; class SqlObject
{
protected SqlConnection conn; // 构造函数,参数为连接串。
protected SqlObject(string strConn)
{
conn = new SqlConnection(strConn);
} // 执行SQL,将结果返回到数据集中。
public DataSet DsQuery(string strSql, params SqlParameter [] paras)
{
SqlCommand cmd = new SqlCommand(strSql, conn);
foreach (SqlParameter para in paras)
{
cmd.Parameters.Add(para);
}
SqlDataAdapter apt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
apt.Fill(ds);
return ds;
} // 执行SQL,将结果返回到数据表中。
public DataTable DtQuery(string strSql, params SqlParameter [] paras)
{
return DsQuery(strSql, paras).Tables[0];
} // 执行SQL,将结果返回到数据行中。
public DataRow DrQuery(string strSql, params SqlParameter [] paras)
{
DataTable dt = DsQuery(strSql, paras).Tables[0];
if (dt.Rows.Count == 0) return null;
else return dt.Rows[0];
} // 执行SQL,并返回查询所返回的结果集中第一行的第一列。
public object ScalarQuery(string strSql, params SqlParameter [] paras)
{
SqlCommand cmd = new SqlCommand(strSql, conn);
try
{
cmd.Connection.Open();
foreach (SqlParameter para in paras)
{
cmd.Parameters.Add(para);
}
return cmd.ExecuteScalar();
}
finally
{
cmd.Connection.Close();
}
} // 执行SQL,返回受影响的行数。
public int ExecNonQuery(string strSql, params SqlParameter [] paras)
{
SqlCommand cmd = new SqlCommand(strSql, conn);
cmd.Connection.Open();
foreach (SqlParameter para in paras)
{
cmd.Parameters.Add(para);
}
int n = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return n;
} // 执行SQLs,返回受影响的行数。
public int ExecNonQuerys(params string [] strSqls)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Connection.Open();
int n = 0;
foreach (string strSql in strSqls)
{
cmd.CommandText = strSql;
n += cmd.ExecuteNonQuery();
}
cmd.Connection.Close();
return n;
} } // End of class SqlObject
} // End of namespace Skyiv
//: 数据库访问模块 - 中间层 - Sqlnamespace Skyiv
{
using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient; class SqlQuery : SqlObject
{
static string strConn = System.Configuration.ConfigurationSettings.AppSettings["SqlDSN"]; // 构造函数
public SqlQuery() : base(strConn) {} // 查询系统参数(营业日期、营业状态)
public DataRow DrSystemArgs()
{
string strSql = "SELECT * FROM [SystemArgs]";
return DrQuery(strSql);
} // 查询营业员
public DataTable DtOperator()
{
string strSql = "SELECT * FROM [operator] ORDER BY [name]";
return DtQuery(strSql);
} // 计算营业点的用户数(Attribute == '0')
public int UserNumAttr0()
{
object o = ScalarQuery("select count(*) from [user] where [attribute]='0'");
if (o == null) return 0;
return (int)o;
} } // End of class SqlQuery
} // End of namespace Skyiv
string commandString = "select name from dbo.sysobjects where xtype = 'U' and status >0";
SqlDataAdapter ada = new SqlDataAdapter(commandString, connectionString);
DataSet ds = new DataSet();
ada.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
listBox1.Items.Add(ds.Tables[0].Rows[i][0]);
}
string strConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strDBPath ;//其他数据库改连接字即可
OleDbConnection m_Conn = new OleDbConnection(strConnStr);
m_Conn.Open();
string strSQL = "SELECT ****";//相应sql语句
comm = m_Conn.CreateCommand();
comm.CommandText = strSQL;
reader = comm.ExecuteReader();//或者comm.ExecuteNonQuery();
if (reader.Read())
{//以下省略
}
使用Ado.net获取数据库架构信息
http://blog.csdn.net/zhzuo/archive/2004/07/03/33273.aspxADO.NET中的多数据表操作浅析
http://blog.csdn.net/zhzuo/archive/2004/08/06/67016.aspx
http://blog.csdn.net/zhzuo/archive/2004/08/06/67037.aspx
透过vs.net数据窗体向导看Ado.net
http://blog.csdn.net/zhzuo/archive/2005/01/03/238273.aspx如果需要看完成的应用程序代码,建议楼主看
http://windowsclient.net/
上的例子。
结构好一点的,不要贴这些基础的代码啊dataGridList.DataSource = Dt;
//dataGridList.SetDataBinding(Dt,"") ;
ComFun.setDBGridTitle(dataGridList,Dt) ;
GridColumnStylesCollection yDataGridColStyle=dataGridList.TableStyles[0].GridColumnStyles ;
myDataGridColStyle["cmId"].Width = 40 ;
myDataGridColStyle["cmId"].HeaderText = "編號" ;myDataGridColStyle["cmType"].Width = 100 ;
myDataGridColStyle["cmType"].HeaderText = "客戶類型" ;
myDataGridColStyle["cmZoneId"].Width = 100 ;
myDataGridColStyle["cmZoneId"].HeaderText = "區域" ;myDataGridColStyle["cmManMst"].Width = 100 ;
myDataGridColStyle["cmManMst"].HeaderText = "負責人" ;myDataGridColStyle["cmManLink"].Width = 100 ;
myDataGridColStyle["cmManLink"].HeaderText = "聯系人" ;myDataGridColStyle["cmFaxA"].Width = 100 ;
myDataGridColStyle["cmFaxA"].HeaderText = "傳真A" ;myDataGridColStyle["cmFaxB"].Width = 100 ;
myDataGridColStyle["cmFaxB"].HeaderText = "傳真B" ;myDataGridColStyle["cmClass"].Width = 100 ;
myDataGridColStyle["cmClass"].HeaderText = "客戶等級" ;myDataGridColStyle["cmInvAdds"].Width = 100 ;
myDataGridColStyle["cmInvAdds"].HeaderText = "發票地址" ;myDataGridColStyle["cmAddressCh"].Width = 100 ;
myDataGridColStyle["cmAddressCh"].HeaderText = "公司中文地址" ;myDataGridColStyle["cmAddressEn"].Width = 100 ;
myDataGridColStyle["cmAddressEn"].HeaderText = "公司英文地址" ;myDataGridColStyle["cmMoneyType"].Width = 100 ;
myDataGridColStyle["cmMoneyType"].HeaderText = "幣別代碼" ;myDataGridColStyle["cmMoneyName"].Width = 100 ;
myDataGridColStyle["cmMoneyName"].HeaderText = "幣別名稱" ;myDataGridColStyle["cmBuyType"].Width = 100 ;
myDataGridColStyle["cmBuyType"].HeaderText = "交易類型" ;myDataGridColStyle["cmNameCh"].Width = 100 ;
myDataGridColStyle["cmNameCh"].HeaderText = "公司中文名稱" ;myDataGridColStyle["cmNameEn"].Width = 100 ;
myDataGridColStyle["cmNameEn"].HeaderText = "公司英文名稱" ;myDataGridColStyle["cmHomePage"].Width = 100 ;
myDataGridColStyle["cmHomePage"].HeaderText = "公司主頁" ;myDataGridColStyle["cmTelA"].Width = 100 ;
myDataGridColStyle["cmTelA"].HeaderText = "電話A" ;myDataGridColStyle["cmTelB"].Width = 100 ;
myDataGridColStyle["cmTelB"].HeaderText = "電話B" ;myDataGridColStyle["cmMemo"].Width = 100 ;
myDataGridColStyle["cmMemo"].HeaderText = "備註" ;