C#.NET连接各种数据库及数据操作Demo

C#.NET连接数据库及数据操作Demo

1.连接Access

string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=D:\\MY\\DataBase\\Access\\testdb.mdb;Persist Security Info=True";

OleDbConnection myCon = new OleDbConnection(strCon);

myCon.Open();

string strSQL = "SELECT * FROM student";

OleDbCommand myCom = new OleDbCommand(strSQL,myCon);

OleDbDataReader dtReader = myCom.ExecuteReader();

Response.Write("

");

while (dtReader.Read())

{

Response.Write("

");

Response.Write("

");

Response.Write("

");

Response.Write("

");

Response.Write("

");

}

Response.Write("

学号	姓名	出生年月	专业	分数
"+dtReader["sid"].ToString()+"	" + dtReader["sname"] + "	" + dtReader["birth"] + "	" + dtReader["major"] + "	" + dtReader["score"] + "

");

dtReader.Close();

myCon.Close();

2.连接SQL

string strCon = "Server=.;Initial Catalog=Northwind;user id=sa;password=sa;";

SqlConnection conn = new SqlConnection(strCon);

conn.Open();

string strSQL = "SELECT [CustomerID],[CompanyName],[ContactName],[ContactTitle]"+ "FROM [Northwind].[dbo].[Customers]"+

"WHERE [ContactTitle] LIKE '%Sales%'";

SqlCommand command = new SqlCommand(strSQL,conn);

SqlDataReader sqlread = command.ExecuteReader();

Response.Write("

");

while (sqlread.Read())

{

Response.Write("

");

Response.Write("

");

Response.Write("

");

Response.Write("

");

Response.Write("

");

Response.Write("

");

}

Response.Write("

客户编号" + "	公司名	客户名	公司职位
" + sqlread["CustomerID"].ToString() + "	" + sqlread["CompanyName"].ToString() + "	" + sqlread["ContactName"].ToString() + "	" + sqlread["ContactTitle"].ToString() + "

");

3.连接读取配置文件插入操作

SqlConnection conn = new

SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ConnectionString);

SqlCommand command = new SqlCommand();

command.Connection = conn;

conn.Open();

https://www.360docs.net/doc/a711557025.html,mandText = "INSERT INTO Employees(FirstName,LastName,BirthDate) Values(@FirstName,@LastName,@BirthDate)";

command.Parameters.Add("@FirstName",SqlDbType.NVarChar);

command.Parameters.Add("@LastName",SqlDbType.NVarChar);

command.Parameters.Add("@BirthDate",SqlDbType.NVarChar);

command.Parameters["@FirstName"].Value = FirstName.Text;

command.Parameters["@LastName"].Value = LastName.Text;

command.Parameters["@BirthDate"].Value = DateTime.Parse(BirthDate.Text.ToString());

command.ExecuteNonQuery();

4.

SqlConnection conn = new

SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ConnectionString);

try

{

conn.Open();

string sql = "SELECT OrderID,OrderDate FROM Orders Where OrderDate>='1998-01-01'";

SqlDataAdapter sqladpt = new SqlDataAdapter(sql, conn);

SqlCommandBuilder bb = new SqlCommandBuilder(sqladpt);

DataSet dt = new DataSet();

sqladpt.Fill(dt);

DataTable table = new DataTable();

table = dt.Tables[0];

Response.Write("

");

for (int i = 0; i < table.Rows.Count; i++)

{

Response.Write("

");

Response.Write("

");

}

Response.Write("

订单编号	订单日期
"+table.Rows[i]["OrderID"]+"	"+table.Rows[i]["OrderDate"]+"

");

}

catch

{

Response.Write("数据填充失败！");

}

5.数据填充

SqlConnection conn = new

SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ConnectionString);

conn.Open();

string sql = "SELECT * FROM Employees WHERE EmployeeID=0";

SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);

SqlCommandBuilder byy = new SqlCommandBuilder(adapter);

DataTable table = new DataTable();

adapter.Fill(table);

DataRow row = table.NewRow();

row["FirstName"] = FirstName.Text;

row["LastName"] = LastName.Text;

row["BirthDate"] = DateTime.Parse(BirthDate.Text.ToString());

table.Rows.Add(row);

adapter.Update(table);

6．数据填充

SqlConnection conn = new

SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ConnectionString);

SqlTransaction trans = null;

SqlCommand command = new SqlCommand();

try {

conn.Open();

trans = conn.BeginTransaction();

command.Connection = conn;

command.Transaction = trans;

https://www.360docs.net/doc/a711557025.html,mandText = "INSERT INTO Employees(FirstName,LastName,BirthDate) Values(@FirstName,@LastName,@BirthDate)";

command.Parameters.Add("@FirstName", SqlDbType.NVarChar);

command.Parameters.Add("@LastName", SqlDbType.NVarChar);

command.Parameters.Add("@BirthDate", SqlDbType.NVarChar);

command.Parameters["@FirstName"].Value = FirstName.Text;

command.Parameters["@LastName"].Value = LastName.Text;

command.Parameters["@BirthDate"].Value = DateTime.Parse(BirthDate.Text.ToString());

command.ExecuteNonQuery();

https://www.360docs.net/doc/a711557025.html,mit();

Response.Write("添加成功！");

}

catch

{

trans.Rollback();

Response.Write("添加失败！");

}

7．配置文件Web.config