TA貢獻(xiàn)1844條經(jīng)驗(yàn) 獲得超8個(gè)贊
有個(gè)問(wèn)題 剛好符合你的要求：
問(wèn)題：
當(dāng)前目錄下的Example.mdb數(shù)據(jù)庫(kù)（這個(gè)是Access數(shù)據(jù)庫(kù)）中，內(nèi)含一個(gè)數(shù)據(jù)表student，有三個(gè)字段：學(xué)號(hào)、姓名、密碼，并有如干記錄。連接串為string connStr="provider=microsoft.jet.oledb.4.0;datasource="+Server.MapPath("Example.mdb")，數(shù)據(jù)庫(kù)對(duì)象不是SQL，而是Access，采用OleDb，即將對(duì)象前SQL換成OleDb。
1、編寫一段程序，將student中的數(shù)據(jù)在DataGridl中顯示出來(lái)。
2、編寫一段程序，將學(xué)號(hào)、姓名、密碼分別為0001，張山，abcd的學(xué)生記錄插入到student中。
3、編寫一段程序，判斷數(shù)據(jù)庫(kù)中是否存在學(xué)號(hào)為“01”、口令為“1234”的學(xué)生。
首先連接數(shù)據(jù)庫(kù)（這里的鏈接字符串是在配置文件中）
static string connectionString = ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString;
private OleDbConnection connection;
public OleDbConnection Connection {
get{
if (connection == null){
connection = new OleDbConnection(connectionString);}
else if (connection.State == System.Data.ConnectionState.Closed) //關(guān)閉
{
connection.Open();}
else if (connection.State == System.Data.ConnectionState.Broken) //中斷
{connection.Close();
connection.Open();}
return connection;
}
}
然后是執(zhí)行查詢語(yǔ)句和插入語(yǔ)句的方法；
/// <summary>
/// 執(zhí)行SQL語(yǔ)句，返回影響的記錄數(shù)
/// </summary>
/// <param name="SQLString">SQL語(yǔ)句</param>
/// <returns>影響的記錄數(shù)</returns>
public static int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
using (OleDbCommand cmd = new OleDbCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.OleDb.OleDbException E)
{
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 執(zhí)行查詢語(yǔ)句，返回OleDbDataReader
/// </summary>
/// <param name="strSQL">查詢語(yǔ)句</param>
/// <returns>OleDbDataReader</returns>
public static OleDbDataReader ExecuteReader(string strSQL)
{
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(strSQL, connection);
try
{
connection.Open();
OleDbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (System.Data.OleDb.OleDbException e)
{
throw new Exception(e.Message);
}
}
下面是查詢方法和插入方法
（由于時(shí)間問(wèn)題，你把數(shù)據(jù)庫(kù)字段都換成你自己的，我這里就不換了）
/// <summary>
/// 增加一條數(shù)據(jù)
/// </summary>
public int Add()
{
StringBuilder strSql=new StringBuilder();
strSql.Append("insert into [student](");
strSql.Append("[stNum],[Name],[Psd])");
strSql.Append(" values (");
strSql.Append("@stNum,@Name,@Psd)");
OleDbParameter[] parameters = {
new OleDbParameter("@stNum", OleDbType.VarChar,50),
new OleDbParameter("@Name", OleDbType.VarChar),
new OleDbParameter("@Psd", OleDbType.VarChar,50)};
parameters[0].Value = "0001";
parameters[1].Value = "張山";
parameters[2].Value = “abcd”;
int num=ExecuteCommand(strSql.ToString(),parameters);
return num;
}
/// <summary>
/// 得到所有對(duì)象
/// </summary>
/// <returns></returns>
public IList<Student> GetAll()
{
IList<Student> modelList = new List<Student>();
string sqlStr = "select * from [student] ORDER BY [Id] DESC";
using (OleDbDataReader reader = DbHelperOleDb.ExecuteReader(sqlStr))
{
while (reader.Read())
{
Student model = new Student();
if (reader["Id"].ToString() != "")
{
model.Id = int.Parse(reader["Id"].ToString());
}
model.stNum = reader["stNum"].ToString();
model.Name = reader["Name"].ToString();
model.Psd = reader["Psd"].ToString();
modelList.Add(model);
}
}
return modelList;
}
頁(yè)面調(diào)用添加方法就可以添加一條記錄了
下面市頁(yè)面綁定數(shù)據(jù)
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataBindGV();
}
}
public void DataBindGV()
{
//GetAll()方法我是放在Student類里面的
gvStudentList.DataSource = new sutdent().GetAll();
gvStudentList.DataBind();
}
頁(yè)面DataGrid的代碼如下
<asp:GridView ID="gvStudentList" runat="server" BorderWidth="0px" BackColor="#999999"
CellPadding="1" CellSpacing="1" Width="100%"
AutoGenerateColumns="False">
<EmptyDataTemplate>
沒(méi)有資料!
</EmptyDataTemplate>
<HeaderStyle Height="20px" BackColor="#CCCCCC" />
<FooterStyle />
<RowStyle BackColor="#fafafa" Height="20px" />
<EmptyDataRowStyle BackColor="#ffffff" />
<SelectedRowStyle />
<Columns>
<asp:BoundField DataField="Title" HeaderText="學(xué)號(hào)" SortExpression="stNum">
<ItemStyle Width="170px" />
</asp:BoundField>
<asp:TemplateField HeaderText="名稱" SortExpression="Name">
<ItemTemplate>
<%#Eval("Name")) %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="密碼" SortExpression="Psd">
<ItemTemplate>
<%#Eval("Psd") %>
</ItemTemplate>
<ItemStyle Width="90px" />
</asp:TemplateField>
</Columns>
<PagerStyle BackColor="#eeeeee" BorderWidth="0" />
</asp:GridView>
關(guān)于第3個(gè)，只需把GetAll()方法的查詢語(yǔ)句換一下就行了，換成如下語(yǔ)句：
select * from [student] where [stNum]='01' and [Psd]='1234'
然后再查詢，根據(jù)查詢的結(jié)果List判斷是否存在，當(dāng)然你也可以執(zhí)行
select count(1) from [student] where [stNum]='01' and [Psd]='1234'
判斷返回的數(shù)是否大于零就行了，當(dāng)然這個(gè)查詢要另外的查詢語(yǔ)句才行用上面的查詢方法不行了，需要執(zhí)行ExecuteScalar()了，自己弄吧，很簡(jiǎn)單的
如此你的問(wèn)題全都解決了
TA貢獻(xiàn)1877條經(jīng)驗(yàn) 獲得超1個(gè)贊
create database student
on primary
(
name='student_data',
filename='f:\db_Stu.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
name='student_log',
filename=''f:\db_Stu_log.ldf'
)
go
TA貢獻(xiàn)1828條經(jīng)驗(yàn) 獲得超3個(gè)贊
create database db_stu
on
(
filename='F:\ db_Stu.mdf',
size=8M
)
log on
(
filename='F:\ db_Stu.log',
size=50M
)
舉報(bào)