小弟现在想从ASP过渡到ASP.NET,可是看了数之后对于ASP.NET数据库的查询、修改、添加、删除方法还不是很清楚求助大家,还有ASP中判断是否数据库里有数据是rs.eof和rs.bof,ASP.NET里是什么?ASP中输出指定的单个字段是<%=rs("字段名")%>,ASP.NET里怎么弄?大概总结了下就这些请大家帮帮忙在这先谢过了!!!!!!<%
'//查询方法
'//----------------------------(1)-------------------------------
Set rs = Server.CreateObject("ADODB.RecordSet")
Sql = "Select * From Users"
Rs.Open Sql,Conn,1,1
If Not Rs.Eof Then
UserName = Rs("UserName")
Password = Rs("Password")
End if
Rs.Close
Set Rs = Nothing
'//----------------------------(2)-------------------------------
Sql = "Select * From Users"
Set Rs = Conn.Execute(Sql)
If Not Rs.Eof Then
LoginName = RsFind("LoginName")
UserName = RsFind("UserName")
Password = RsFind("Password")
End if
Rs.Close
Set Rs = Nothing
'//修改方法
'//----------------------------(1)-------------------------------
Set Rs = Server.CreateObject("ADODB.RecordSet")
Sql = "Select * From Users"
Rs.Open Sql,Conn,1,1
If Not Rs.Eof Then
Rs("UserName") = UserName
Rs("Password") = Password
Rs.Update
Update = True
Else
Update = False
End if
Rs.Close
Set Rs = Nothing
'//----------------------------(2)-------------------------------
sql = "Update Users Set UserName=" & serName & ", Password=" & Password
Conn.Execute(sql)
'//添加方法
'//----------------------------(1)-------------------------------
Set Rs = Server.CreateObject("ADODB.RecordSet")
Sql = "Select * From Users"
Rs.Open Sql,Conn,1,3
If Rs.Eof Then
Rs.AddNew
Rs("LoginName") = LoginName
Rs("UserName") = UserName
Rs("Password") = Md5(Password)
Rs.Update
NewRecord = True
Else
NewRecord = False
End if
Rs.Close
Set Rs = Nothing
'//----------------------------(2)-------------------------------
sql = "Insert Into Users(UserName, Password) Values(" & UserName & "," & Password & ")"
Conn.Execute(sql)
'//删除方法
'//----------------------------(1)-------------------------------
Set Rs = Server.CreateObject("ADODB.RecordSet")
sql = "Delete From Users Where UsersId=" & tUserID
Rs.Open sql,Conn,1,3
Rs.Close
Set Rs = Nothing
'//----------------------------(2)-------------------------------
sql = "Delete From Users Where UsersId=" & tUserID
Conn.Execute(sql)
%>
'//查询方法
'//----------------------------(1)-------------------------------
Set rs = Server.CreateObject("ADODB.RecordSet")
Sql = "Select * From Users"
Rs.Open Sql,Conn,1,1
If Not Rs.Eof Then
UserName = Rs("UserName")
Password = Rs("Password")
End if
Rs.Close
Set Rs = Nothing
'//----------------------------(2)-------------------------------
Sql = "Select * From Users"
Set Rs = Conn.Execute(Sql)
If Not Rs.Eof Then
LoginName = RsFind("LoginName")
UserName = RsFind("UserName")
Password = RsFind("Password")
End if
Rs.Close
Set Rs = Nothing
'//修改方法
'//----------------------------(1)-------------------------------
Set Rs = Server.CreateObject("ADODB.RecordSet")
Sql = "Select * From Users"
Rs.Open Sql,Conn,1,1
If Not Rs.Eof Then
Rs("UserName") = UserName
Rs("Password") = Password
Rs.Update
Update = True
Else
Update = False
End if
Rs.Close
Set Rs = Nothing
'//----------------------------(2)-------------------------------
sql = "Update Users Set UserName=" & serName & ", Password=" & Password
Conn.Execute(sql)
'//添加方法
'//----------------------------(1)-------------------------------
Set Rs = Server.CreateObject("ADODB.RecordSet")
Sql = "Select * From Users"
Rs.Open Sql,Conn,1,3
If Rs.Eof Then
Rs.AddNew
Rs("LoginName") = LoginName
Rs("UserName") = UserName
Rs("Password") = Md5(Password)
Rs.Update
NewRecord = True
Else
NewRecord = False
End if
Rs.Close
Set Rs = Nothing
'//----------------------------(2)-------------------------------
sql = "Insert Into Users(UserName, Password) Values(" & UserName & "," & Password & ")"
Conn.Execute(sql)
'//删除方法
'//----------------------------(1)-------------------------------
Set Rs = Server.CreateObject("ADODB.RecordSet")
sql = "Delete From Users Where UsersId=" & tUserID
Rs.Open sql,Conn,1,3
Rs.Close
Set Rs = Nothing
'//----------------------------(2)-------------------------------
sql = "Delete From Users Where UsersId=" & tUserID
Conn.Execute(sql)
%>
解决方案 »
- 求VS2008 or VS2010 水晶报表明细教程
- 怎样实现不同用户登陆后界面不同?
- 救命
- 前后台登录能统一成一个接口吗?
- 跪求给位给我个在asp.net中发送emaild实例
- Atlas的'Sys'未定义错误怎么解决
- 群发邮件问题探讨
- 一个关于新闻发布系统的问题,希望大家来畅所欲言,给个意见,先谢过了!!
- uploadify上传文件控件后台如何获取它的文件大小和名字
- 求大神: <span id="lbl1">your enter: <script>alert('123')</script></span>
- asp.net控件不显示
- 哪个软件安装制作工具可以在安装我自己的Winform程序的同时,还可以安装framework。
查询方法:/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query(string SQLString)
{
Page page = new Page();
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + page.Server.MapPath("data/%download.mdb");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}调用:string strsql = "select * from T_download";
DataSet ds = Query(strsql);执行SQL语句:/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
Page page = new Page();
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + page.Server.MapPath("../data/%20090330download.mdb");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.OleDb.OleDbException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}插入,修改,删除都可使用,调用方法:修改:
string strSql = "update T_download set filename='" + strName + "',url='" + strUrl + "' where id=" + Request["id"].Trim();
if (ExecuteSql(strSql) > 0)
{
AlertAndRedirect("修改成功!", "download.aspx");
}
插入:
string strSql = "insert into T_download (filename,d_count,url) values ('" + strName + "',1,'" + strUrl + "')";
if (ExecuteSql(strSql) > 0)
{
AlertAndRedirect("添加成功!", "download.aspx");
}
删除同上
http://download.csdn.net/source/862962
表名 office
字段 officeName(vachar(30)),officeType(vachar(30)),department(vachar(30))
代码:
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace Test
{
/// <summary>
/// Form1 的摘要说明。
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
//声明连接
private SqlConnection conn;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.TextBox textBox2;
private System.Windows.Forms.TextBox textBox3;
private System.Windows.Forms.Button button2;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button4;
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();
//
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
}
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.textBox1 = new System.Windows.Forms.TextBox();
this.textBox2 = new System.Windows.Forms.TextBox();
this.textBox3 = new System.Windows.Forms.TextBox();
this.button2 = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.button4 = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(56, 56);
this.button1.Name = "button1";
this.button1.TabIndex = 0;
this.button1.Text = "新增";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(152, 272);
this.textBox1.Name = "textBox1";
this.textBox1.Size = new System.Drawing.Size(120, 21);
this.textBox1.TabIndex = 2;
this.textBox1.Text = "";
//
// textBox2
//
this.textBox2.Location = new System.Drawing.Point(304, 272);
this.textBox2.Name = "textBox2";
this.textBox2.Size = new System.Drawing.Size(120, 21);
this.textBox2.TabIndex = 2;
this.textBox2.Text = "";
//
// textBox3
//
this.textBox3.Location = new System.Drawing.Point(464, 272);
this.textBox3.Name = "textBox3";
this.textBox3.Size = new System.Drawing.Size(120, 21);
this.textBox3.TabIndex = 2;
this.textBox3.Text = "";
//
// button2
//
this.button2.Location = new System.Drawing.Point(56, 104);
this.button2.Name = "button2";
this.button2.TabIndex = 0;
this.button2.Text = "修改";
this.button2.Click += new System.EventHandler(this.button2_Click);
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(152, 16);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(504, 248);
this.dataGrid1.TabIndex = 1;
this.dataGrid1.CurrentCellChanged += new System.EventHandler(this.dataGrid1_CurrentCellChanged_1);
//
// button4
//
this.button4.Location = new System.Drawing.Point(56, 152);
this.button4.Name = "button4";
this.button4.TabIndex = 0;
this.button4.Text = "删除";
this.button4.Click += new System.EventHandler(this.button4_Click);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(664, 318);
this.Controls.Add(this.textBox1);
this.Controls.Add(this.button1);
this.Controls.Add(this.textBox2);
this.Controls.Add(this.textBox3);
this.Controls.Add(this.button2);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.button4);
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void btn_lj_Click(object sender, System.EventArgs e)
{
//连接串
string str="Server=(local);uid=sa;pwd=;database=hospital";
//连接
conn=new SqlConnection(str);
//sql
string sql="select officeName as 科室名称 ,officeType as 科别, "+
"department as 所属部门 from office ";
//适配器
SqlDataAdapter sqldap=new SqlDataAdapter(sql,this.conn);
//内存表
DataTable dt=new DataTable();
//填充
sqldap.Fill(dt);
//数据绑定
this.dataGrid1.DataSource=dt;
}
//刷新方法
public void myreflush()
{
string sql="select officeName as 科室名称 ,officeType as 科别, "+
"department as 所属部门 from office ";
SqlDataAdapter sqldap=new SqlDataAdapter(sql,this.conn);
DataTable dt=new DataTable();
sqldap.Fill(dt);
this.dataGrid1.DataSource=dt;
} private void button1_Click(object sender, System.EventArgs e)
{
//新增
if(this.conn.State==ConnectionState.Closed)
{
this.conn.Open();
}
string sql="insert into office values('"+this.textBox1.Text+"','"+this.textBox2.Text+"','"+this.textBox3.Text+"')";
SqlCommand sqlcmd=new SqlCommand(sql,this.conn);
int i=sqlcmd.ExecuteNonQuery();
if(i!=0)
{
MessageBox.Show("Ok");
this.myreflush();
}
else
{
MessageBox.Show("No");
}
}
//连动
private void dataGrid1_CurrentCellChanged_1(object sender, System.EventArgs e)
{
//获取当前选择行数
int iCount=this.dataGrid1.CurrentCell.RowNumber;
//数据来源
DataTable dt=(DataTable)this.dataGrid1.DataSource;
try
{
this.textBox1.Text=Convert.ToString(dt.Rows[iCount]["科室名称"]);
this.textBox2.Text=Convert.ToString(dt.Rows[iCount]["科别"]);
this.textBox3.Text=Convert.ToString(dt.Rows[iCount]["所属部门"]);
}
catch(System.IndexOutOfRangeException)
{
MessageBox.Show("此行不存在");
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
//连接串
string str="Server=(local);uid=sa;pwd=;database=hospital";
//连接
conn=new SqlConnection(str);
//sql
string sql="select officeName as 科室名称 ,officeType as 科别, "+
"department as 所属部门 from office ";
//适配器
SqlDataAdapter sqldap=new SqlDataAdapter(sql,this.conn);
//内存表
DataTable dt=new DataTable();
//填充
sqldap.Fill(dt);
//数据绑定
this.dataGrid1.DataSource=dt;
}
private void button4_Click(object sender, System.EventArgs e)
{
//删除
DialogResult dr=MessageBox.Show("是否真的删除"+this.textBox1.Text,"消息",MessageBoxButtons.OKCancel);
if(dr==DialogResult.OK)
{
if(this.conn.State==ConnectionState.Closed)
{
this.conn.Open();
}
string sql="delete from office where officeName='"+this.textBox1.Text+"'";
SqlCommand sqlcmd=new SqlCommand(sql,this.conn);
int i=sqlcmd.ExecuteNonQuery();
if(i!=0)
{
MessageBox.Show("OK");
}
else
{
MessageBox.Show("No");
}
this.myreflush();
}
}
private void button2_Click(object sender, System.EventArgs e)
{
//修改
if(this.conn.State==ConnectionState.Closed)
{
this.conn.Open();
}
string sql="update office set officeType='"+this.textBox2.Text+"',department='"+this.textBox3.Text+"'"
+"where officeName='"+this.textBox1.Text+"'";
SqlCommand sqlcmd=new SqlCommand(sql,this.conn);
int i=sqlcmd.ExecuteNonQuery();
if(i!=0)
{
MessageBox.Show("OK");
this.myreflush();
}
else
{
MessageBox.Show("No");
}
}}
}
查询:/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query(string SQLString)
{
Page page = new Page();
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + page.Server.MapPath("../data/%20090330download.mdb");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}调用方法:string strsql = "select * from T_download";
DataSet ds = Query(strsql);
if (ds.Tables[0].Rows.Count > 0)
{
txtName.Text = ds.Tables[0].Rows[0]["filename"].ToString();//将DS中数据赋值给文本框
txtUrl.Text = ds.Tables[0].Rows[0]["url"].ToString();
}
执行update,insert,delete/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
Page page = new Page();
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + page.Server.MapPath("../data/%20090330download.mdb");
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.OleDb.OleDbException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}调用方法:
插入string strSql = "insert into T_download (filename,d_count,url) values ('" + strName + "',1,'" + strUrl + "')";
if (ExecuteSql(strSql) > 0)
{
AlertAndRedirect("添加成功!", "download.aspx");
}修改:string strSql = "update T_download set filename='" + strName + "',url='" + strUrl + "' where id=" + Request["id"].ToString().Trim();
if (ExecuteSql(strSql) > 0)
{
AlertAndRedirect("修改成功!", "download.aspx");
}删除同上。
下列代码为针对MSSQL数据库:/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
}
微软的msdn上都有例子