如何用C#连接数据库并新增,删除,修改？

Question

//连接数据库
string strConnection="Provider=Microsoft.Jet.OleDb.4.0;";
strConnection+=@"Data Source=DataBase\DB.mdb";
OleDbConnection Conn=new OleDbConnection(strConnection);// string dw=this.txtdwmc.Text.ToString();
// string jsyxm=this.txtjsyxm.Text.ToString(); //添加记录时的SQL命令
string insert="INSERT INTO jiaotong (dw,jsyxm,jszh,lxdh,jsyqz) VALUES ('"+this.txtdwmc.Text+"','"+this.txtjsyxm.Text+"','"+this.txtjszh.Text+"','"+this.txtlxdh.Text+"','"+this.txtbz.Text+"')";

//初始化OleDbCommand
OleDbCommand Comm=new OleDbCommand();
Comm.CommandText=insert;
Comm.Connection=Conn; //打开连接
Conn.Open(); //执行添加记录的语句
Comm.ExecuteNonQuery(); //关闭连接
Conn.Close();

Answer 1

//与SQL的连接
//查询 
SqlConnection conn = new SqlConnection("server=localhost;database=northwind;uid=sa;pwd=;");
        try
        {
            SqlCommand  cmd= conn.CreateCommand();
            cmd.CommandText = "select  * from categories";
            cmd.CommandType = CommandType.Text;
            conn.Open();
            SqlDataReader datareader = cmd.ExecuteReader();
//以下是把查询出来的数据直接生成表
            Response.Write("<table border=1");
            Response.Write("<tr><th>CategoryID</th><th>CategoryName</th><th>Description</th></tr>");
            while (datareader.Read())
            {
                Response.Write("<tr>");
                Response.Write("<td align='center'>"+datareader["CateGoryID"]+"</td>" );
               
                
                Response.Write("<td align='center'>" + datareader["Categoryname"] + "</td>");
                Response.Write("<td align='left'>" + datareader["Description"] + "</td>");
                 Response.Write("</tr>");
            }
            Response.Write("</table>");
            datareader.Close();
            conn.Close();
        }
        finally
        {
            if (conn != null)
                conn.Dispose();
        }
//增加记录
        try
        {
            SqlCommand  cmd= conn.CreateCommand();
            cmd.CommandText = "insert into 表名(列1,列2,列3) values(@列1,@列2,@列2)categories";            cmd.CommandType = CommandType.Text;
            conn.Open();
            SqlDataReader datareader = cmd.ExecuteReader();            Response.Write("<table border=1");
            Response.Write("<tr><th>CategoryID</th><th>CategoryName</th><th>Description</th></tr>");
            while (datareader.Read())
            {
                Response.Write("<tr>");
                Response.Write("<td align='center'>"+datareader["CateGoryID"]+"</td>" );
               
                
                Response.Write("<td align='center'>" + datareader["Categoryname"] + "</td>");
                Response.Write("<td align='left'>" + datareader["Description"] + "</td>");
                 Response.Write("</tr>");
            }
            Response.Write("</table>");
            datareader.Close();
            conn.Close();
        }
        finally
        {
            if (conn != null)
                conn.Dispose();
        }

Answer 2

//与SQL的连接
//查询 
SqlConnection conn = new SqlConnection("server=localhost;database=northwind;uid=sa;pwd=;");
        try
        {
            SqlCommand  cmd= conn.CreateCommand();
            cmd.CommandText = "select  * from categories";
            cmd.CommandType = CommandType.Text;
            conn.Open();
            SqlDataReader datareader = cmd.ExecuteReader();
//以下是把查询出来的数据直接生成表
            Response.Write("<table border=1");
            Response.Write("<tr><th>CategoryID</th><th>CategoryName</th><th>Description</th></tr>");
            while (datareader.Read())
            {
                Response.Write("<tr>");
                Response.Write("<td align='center'>"+datareader["CateGoryID"]+"</td>" );
                
                
                Response.Write("<td align='center'>" + datareader["Categoryname"] + "</td>");
                Response.Write("<td align='left'>" + datareader["Description"] + "</td>");
                 Response.Write("</tr>");
            }
            Response.Write("</table>");
            datareader.Close();
            conn.Close();
        }
        finally
        {
            if (conn != null)
                conn.Dispose();
        }
//增加记录
        try
        {
            SqlCommand  cmd= conn.CreateCommand();
            cmd.CommandText = "insert into 表名(列1,列2,列3...) values(@列1,@列2,@列2...)categories";
            
            cmd.CommandType = CommandType.Text;
                        //设置参数列1
            SqlParameter 列1=new SqlPrameter();
            列1.ParameterName="@列1";
            列1.DbType=DbType.String;
            cmd.Parameters.Add(列1);
            //设置参数列2
            SqlParameter 列2=new SqlPrameter();
            列2.ParameterName="@列2";
            列2.DbType=DbType.String;
            cmd.Parameters.Add(列2);
            //设置参数列3
            SqlParameter 列3=new SqlPrameter();
            列3.ParameterName="@列3";
            列3.DbType=DbType.String;
            cmd.Parameters.Add(列3);
            //......
            //执行SQL语名并且返回影响行数
              conn.Open();
            int rowAffected=0
            rowAffected=cmd.ExecuteNonQuery();
             
            conn.Close();
        }
        finally
        {
            if (conn != null)
                conn.Dispose();
        }

Answer 3

用sqlDataSource或AccessDataSource控件，然後根據向導去設置就可以了

Answer 4

有很多方法的,我用的是对存储过程的操作.封装一下,哪里都可以用,只要把存储过程的名称写入就能得到DataSet或者SqlDataReader.网上有很多.如果找不到,我可以发一个给你.

Answer 5

 private void Form1_Load(object sender, System.EventArgs e)
{
  con=new SqlConnection("database=master;uid=sa");
  ds=new DataSet();
  da=new SqlDataAdapter("select * from axl1",con);
  da.Fill(ds,"axl1");
  dataGrid1.DataSource=ds.Tables[0].DefaultView;
if(ds.Tables["axl1"].Rows.Count==0)
{
  bntdelete.Enabled=false;
  btnupdate.Enabled=false;
}
  
} private void btninsert_Click(object sender, System.EventArgs e)
{
con.Open();
string str="insert into axl1 values(@FlightCode, @Password , @Name ,@SeatNo, @Sex , @Age )";
da.InsertCommand=new SqlCommand(str,con); sp=da.InsertCommand.Parameters.Add("@FlightCode",SqlDbType.Char);
sp.SourceColumn="FlightCode";
sp.SourceVersion=DataRowVersion.Current; sp=da.InsertCommand.Parameters.Add("@Password",SqlDbType.VarChar);
sp.SourceColumn="Password";
sp.SourceVersion=DataRowVersion.Current; sp=da.InsertCommand.Parameters.Add("@Name",SqlDbType.VarChar);
sp.SourceColumn="Name";
sp.SourceVersion=DataRowVersion.Current; sp=da.InsertCommand.Parameters.Add("@SeatNo",SqlDbType.Int);
sp.SourceColumn="SeatNo";
sp.SourceVersion=DataRowVersion.Current; sp=da.InsertCommand.Parameters.Add("@Sex",SqlDbType.VarChar);
sp.SourceColumn="Sex";
sp.SourceVersion=DataRowVersion.Current; sp=da.InsertCommand.Parameters.Add("@Age",SqlDbType.VarChar);
sp.SourceColumn="Age";
sp.SourceVersion=DataRowVersion.Current; try
{
da.Update(ds,"axl1");
MessageBox.Show("已经插入"); }
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
MessageBox.Show("插入失败");
}
finally
{
  con.Close();
}
if(ds.Tables["axl1"].Rows.Count>1)
{
  bntdelete.Enabled=true;
btnupdate.Enabled=true;
} } private void btnupdate_Click(object sender, System.EventArgs e)
{
 
   string update="update axl1 set FlightCode=@FlightCode,Password=@Password,Name=@Name,SeatNo=@SeatNo,Sex=@Sex,Age=@Age where FlightCode=@FlightCode";
   da.UpdateCommand=new SqlCommand(update,con); sp=da.UpdateCommand.Parameters.Add("@FlightCode",SqlDbType.VarChar);
sp.SourceColumn="FlightCode";
sp.SourceVersion=DataRowVersion.Current; sp=da.UpdateCommand.Parameters.Add("@Password",SqlDbType.VarChar);
sp.SourceColumn="Password";
sp.SourceVersion=DataRowVersion.Current; sp=da.UpdateCommand.Parameters.Add("@Name",SqlDbType.VarChar);
sp.SourceColumn="Name";
sp.SourceVersion=DataRowVersion.Current; sp=da.UpdateCommand.Parameters.Add("@SeatNo",SqlDbType.Int);
sp.SourceColumn="SeatNo";
sp.SourceVersion=DataRowVersion.Current; sp=da.UpdateCommand.Parameters.Add("@Sex",SqlDbType.VarChar);
sp.SourceColumn="Sex";
sp.SourceVersion=DataRowVersion.Current; sp=da.UpdateCommand.Parameters.Add("@Age",SqlDbType.VarChar);
sp.SourceColumn="Age";
sp.SourceVersion=DataRowVersion.Current; try
{
 
da.Update(ds,"axl1");
MessageBox.Show("已经更新");
 

}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
  con.Close();
}
if(ds.Tables[0].Rows.Count==0)
{
  bntdelete.Enabled=false;
  btnupdate.Enabled=false;
}   
} private void bntdelete_Click(object sender, System.EventArgs e)
{
string delete="delete from axl1 where FlightCode=@FlightCode";
da.DeleteCommand=new SqlCommand(delete,con); sp=da.DeleteCommand.Parameters.Add("@FlightCode",SqlDbType.VarChar);
sp.SourceColumn="FlightCode";
sp.SourceVersion=DataRowVersion.Current; sp=da.DeleteCommand.Parameters.Add("@Password",SqlDbType.VarChar);
sp.SourceColumn="Password";
sp.SourceVersion=DataRowVersion.Current; sp=da.DeleteCommand.Parameters.Add("@Name",SqlDbType.VarChar);
sp.SourceColumn="Name";
sp.SourceVersion=DataRowVersion.Current; sp=da.DeleteCommand.Parameters.Add("@SeatNo",SqlDbType.Int);
sp.SourceColumn="SeatNo";
sp.SourceVersion=DataRowVersion.Current; sp=da.DeleteCommand.Parameters.Add("@Sex",SqlDbType.VarChar);
sp.SourceColumn="Sex";
sp.SourceVersion=DataRowVersion.Current; sp=da.DeleteCommand.Parameters.Add("@Age",SqlDbType.VarChar);
sp.SourceColumn="Age";
sp.SourceVersion=DataRowVersion.Current; try
{
ds.Tables[0].Rows[dataGrid1.CurrentRowIndex].Delete();
da.Update(ds,"axl1");
MessageBox.Show("已经删除");
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
  con.Close();
}
if(ds.Tables["axl1"].Rows.Count==0)
{
  bntdelete.Enabled=false;
      btnupdate.Enabled=false;
}
}
}
}

Answer 6

在DataGrid上实现对数据库的操作

Answer 7

一个界面,里面用datagrid，实施增　删　改？

Answer 8

楼主应该养成好习惯...查MSDN吧....