c#操作sql和access

最新推荐文章于 2020-07-06 14:47:00 发布

火影忍者2222

最新推荐文章于 2020-07-06 14:47:00 发布

阅读量404

点赞数

分类专栏： CSharp教程文章标签： sql c# dataset string textbox database

CSharp教程专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一操作SQL

1,准备

定义全局变量gsqlconn,gsqlcmd,gsqldap,在下面提及的修改、插入等操作需用上。

操作数据库，用到四大类SqlConnection，SqlCommand，SqlDataAdapter，DataSet

using System.Data.SqlClient;

//定义全局变量gsqlconn,gsqlcmd,gsqldap

SqlConnection gsqlconn = new SqlConnection("data source=(local);database=pubs;user id=sa;pwd=");

SqlCommand gsqlcmd = new SqlCommand();

SqlDataAdapter gsqldap = new SqlDataAdapter();

// sqlconn.Open();//sqlconn.Close();若只是读取数据库，无需Open()

2，读取

DataSet dset = new DataSet();//局部的dset,保持 Fill为全新的数据

gsqlcmd.Connection = gsqlconn;

gsqlcmd.CommandText = "select au_lname from authors";//1

gsqldap.SelectCommand = gsqlcmd;

gsqldap.Fill(dset, "tb1");

gsqlcmd.CommandText = "select * from authors";//2

gsqldap.SelectCommand = gsqlcmd;

gsqldap.Fill(dset, "tb2");

dataGridView3.DataSource = dset.Tables["tb1"];

dataGridView4.DataSource = dset.Tables["tb2"];

3，修改

gsqlconn.Open();

gsqlcmd.Connection = gsqlconn;

gsqlcmd.CommandText="update discounts set discounttype='ET' where discounttype='sb'";

gsqlcmd.ExecuteNonQuery();

gsqlconn.Close();

4，插入

SqlCommand sqlcmd = new SqlCommand();//定义为局部变量－>解决第二次插入出错

gsqlconn.Open();

sqlcmd.Connection = gsqlconn;

sqlcmd.CommandText = "insert into discounts (discounttype,discount) values (@discounttype,@discount)";

sqlcmd.Parameters.Add("@discounttype", SqlDbType.VarChar); sqlcmd.Parameters["@discounttype"].Value = "SB";

sqlcmd.Parameters.Add("@discount", SqlDbType.Decimal); sqlcmd.Parameters["@discount"].Value = 5;

sqlcmd.ExecuteNonQuery();

gsqlconn.Close();

5，删除

gsqlconn.Open();

gsqlcmd.Connection = gsqlconn;

gsqlcmd.CommandText = "delete from discounts where discounttype='ET'";

gsqlcmd.ExecuteNonQuery();

gsqlconn.Close();

6，从dataset中取数据

DataSet dset = new DataSet();//局部的dset,保持 Fill为全新的数据

gsqlcmd.Connection = gsqlconn;

gsqlcmd.CommandText = "select phone,address from authors";

gsqldap.SelectCommand = gsqlcmd;

gsqldap.Fill(dset, "tb3");

dataGridView3.DataSource = dset.Tables["tb3"];

// string buf_data1 = dset.Tables["tb3"].Rows[0][0].ToString();//0行0列

string buf_data1 = dset.Tables["tb3"].Rows[0]["phone"].ToString();//0行"phone"列

textBox8.Text = buf_data1;

7,从dataGridView中取数据

if (dataGridView3.Rows.Count == 0) return;

string buf_data1;

// buf_data1 = dataGridView3.Rows[0].Cells[0].Value.ToString();//0行0列

// buf_data1 = dataGridView3.Rows[0].Cells["phone"].Value.ToString();//0行"phone"列

// buf_data1 = dataGridView3.CurrentRow.Cells["phone"].Value.ToString();//选择行"phone"列

buf_data1 = dataGridView3.CurrentCell.Value.ToString();//当前选择

textBox8.Text = buf_data1;

8,创建数据库

//注意输入"C:\xx" -->"C:\\xx"

string myExecuteQuery = "Create Database AppDta On Primary (name=appDtal,filename='c:\\appdta1.mdf',size=10MB,Maxsize=100MB,filegrowth=10MB),(Name=appdte2,filename='c:\\appdta1.ndf',size=10MB,FileGrowth=10MB)";

SqlConnection mySqlConnection = new SqlConnection("server=(local);user id=sa;password=;Trusted_Connection=yes;");

SqlCommand myCommand = new SqlCommand(myExecuteQuery, mySqlConnection);

myCommand.Connection.Open();

myCommand.ExecuteNonQuery();

mySqlConnection.Close();

9，删除数据库

string myExecuteQuery = "Drop Database AppDta"; //删除AppDta

SqlConnection mySqlConnection = new SqlConnection("server=(local);user id=sa;password=;Trusted_Connection=yes;");

SqlCommand myCommand = new SqlCommand(myExecuteQuery, mySqlConnection);

myCommand.Connection.Open();

myCommand.ExecuteNonQuery();

mySqlConnection.Close();

10,创建数据表

string myExecuteQuery = "Create Table AppDta.dbo.Customer" +

"( CustId int not Null check(CustId>0)," +

" Name char(30) not Null check(Name<>'')," +

"status char(1) not Null Default '')" +

" Use AppDta";

SqlConnection mySqlConnection = new SqlConnection("server=(local);user id=sa;password=;Trusted_Connection=yes;");

SqlCommand myCommand = new SqlCommand(myExecuteQuery, mySqlConnection);

myCommand.Connection.Open();

myCommand.ExecuteNonQuery();

mySqlConnection.Close();

11,删除数据表

string myExecuteQuery = "Drop table AppDta.dbo.Customer";

SqlConnection mySqlConnection = new SqlConnection("server=(local);user id=sa;password=;Trusted_Connection=yes;");

SqlCommand myCommand = new SqlCommand(myExecuteQuery, mySqlConnection);

myCommand.Connection.Open();

myCommand.ExecuteNonQuery();

mySqlConnection.Close();

12,增加列

string myExecuteQuery = "Alter Table AppDta.dbo.Customer Add list1 int Default 0";

SqlConnection mySqlConnection = new SqlConnection("server=(local);user id=sa;password=;Trusted_Connection=yes;");

SqlCommand myCommand = new SqlCommand(myExecuteQuery, mySqlConnection);

myCommand.Connection.Open();

myCommand.ExecuteNonQuery();

mySqlConnection.Close();

//*****************************************************************

二操作Access

与操作SQL用法大同小异

操作日期时间，要加"#",例 "#2008-1-1 0:00:01#"

1,读取记录

using System.Data.OleDb;//add

using System.Data;//add

public bool RDAccess(string Obj, out byte COM, out byte ADDR, out byte CH)//读取Access

{

String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Model.mdb"; OleDbConnection connection = new OleDbConnection(connectionString); //建立连接

OleDbCommand cmd = new OleDbCommand();//使用OleDbCommand类来执行Sql语句：

cmd.Connection = connection;//

cmd.CommandText = "select COM,ADDR,CH from tb1 where Model='" + Obj+"'";//需加单引号

DataSet dset = new DataSet();

OleDbDataAdapter ODbda = new OleDbDataAdapter();

ODbda.SelectCommand = cmd;

ODbda.Fill(dset, "tb1");

COM = byte.Parse(dset.Tables["tb1"].Rows[0]["COM"].ToString());//

ADDR = byte.Parse(dset.Tables["tb1"].Rows[0]["ADDR"].ToString());//

CH = byte.Parse(dset.Tables["tb1"].Rows[0]["CH"].ToString());//

return true;

}