关于与数据库连接、更新数据等-C#日记
上一篇 / 下一篇 2009-06-22 11:04:26 / 个人分类：开发知识
本事例是实现了如何连接数据库，从数据库里取出数据打印控制台或打印文件，用户用命令方式或调用存储过程更新数据记录，调用存储过程后从存储过程返回参数及xml操作等。
代码调用的存储过程
USE [oscartest]
GO
/****** 对象: StoredProcedure [dbo].[myprc] 脚本日期: 06/22/2009 10:53:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[myprc]
@score int ,
@usename nvarchar(50),
@countN int output
as
select @countN =(select count(*) from Student)+1
insert into student(NO,Nname,score) values (@countN,@usename,@score);
代码连接的表
连接字符串：存放在单独的login.cs里
using System;
public class Login
{
// Alter this connection string here - all other examples use this class
public static string Connection
{
//get { return "data source=(local);initial catalog=Northwind;integrated security=SSPI;"; }
get { return "Data Source=172.16.1.72;Initial Catalog=oscartest;User ID=oscar;Password=000;"; }
}
}
主代码
using System;
using System.IO;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Data.OleDb;
/// <summary>
/// Corresponds to section titled 'Executing Commands' in Chapter 11
/// </summary>
public class ExecutingCommands
{
/// <summary>
/// SimpleDataAccess - show SQL & Stored Procs
/// </summary>
public static void Main ( )
{
// The following is the database connection string
string source = Login.Connection ;
// First section of code - using a SQL statement to select records
ExecuteSql ( source ) ;
// Second section - calling a stored procedure
ExecuteStoredProc ( source ) ;
// Third - batch statements
ExecuteBatch ( source ) ;
// Fourth - Return XML from SqlServer...
ExecuteXml ( source ) ;
//DataSet
Dateset1(source);
}
public static void ExecuteSql ( string source )
{
// And this is the SQL statement that will be issued
string select = "SELECT id,Nname,score FROM student;select id from student";
try
{
// Connect to the database...
using ( SqlConnection conn=new SqlConnection(source) )
{
conn.Open ( ) ;
// Create the SQL command...
SqlCommand cmd = new SqlCommand ( select , conn ) ;
// Construct the data reader
using ( SqlDataReader reader = cmd.ExecuteReader ( ) )
{
// Output headings...
Console.WriteLine ( "*** SqlProvider ***" ) ;
Console.WriteLine ( "Output from direct SQL statement..." ) ;
Console.WriteLine ( ) ;
Console.WriteLine ( "ID Name score" ) ;
Console.WriteLine ( "----------------------------------------------------------------" ) ;
// And iterate through the data
while ( reader.Read ( ) )
{
Console.WriteLine ( "{0,-30} {1},{2}" , reader.GetString(0) , reader[1],reader[2] ) ;
}
reader.Close ( ) ;
}
conn.Close ( ) ;
}
}
catch ( Exception e )
{
Console.WriteLine ( e.ToString( ) ) ;
}
}
public static void ExecuteStoredProc(string source)
{
// Connect to the database...
using (SqlConnection conn = new SqlConnection(source))
{
// Open the database connection
conn.Open();
// Create the SQL command that links to a stored procedure
SqlCommand cmd = new SqlCommand("myprc", conn);
// Set the type to stored procedure
cmd.CommandType = CommandType.StoredProcedure;
string usename1 = "test1";
Random rnd = new Random();
// And add the parameter to the stored proc...
SqlParameter score = cmd.Parameters.Add(new SqlParameter("@score", SqlDbType.Int));
SqlParameter usename= cmd.Parameters.Add(new SqlParameter("@usename", SqlDbType.NVarChar, 50));
SqlParameter countN = cmd.Parameters.Add(new SqlParameter("@countN", SqlDbType.Int));
score.Value = rnd.Next(60, 100);
usename.Value = usename1;
countN.Direction = ParameterDirection.Output;
score.Direction = ParameterDirection.Input;
usename.Direction = ParameterDirection.Input;
//cmd.UpdatedRowSource = UpdateRowSource.None;
//cmd.Parameters[0].Value = score1;
//Console.WriteLine(cmd.Parameters[0].Value is Int32);
//cmd.Parameters[1].Value = usename1;
// Construct the data reader
using (SqlDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine("");
Console.WriteLine("*** SqlProvider ***");
Console.WriteLine("Call NorthWind CustOrderHist stored proc for customer 'QUICK'...");
Console.WriteLine();
Console.WriteLine("Product Name Quantity");
Console.WriteLine("---------------------------------------------------------------------");
Console.WriteLine("共有{0}个学生",countN.Value);
reader.Close();
Console.WriteLine();
}
string select = "SELECT id,Nname,score FROM student;";
cmd = new SqlCommand(select, conn);
using (SqlDataReader reader1 = cmd.ExecuteReader())
{ // Iterate through the data
while (reader1.Read())
{
Console.WriteLine("{0,-34} {1} {2}", reader1[0], reader1[1],reader1[2]);
}
}
// Close the connection
conn.Close();
}
}
protected static void ExecuteFullTable ( string source )
{
// Connect to the database...
using ( OleDbConnection conn = new OleDbConnection("Provider=SQLOLEDB;" + source) )
{
// Open the database connection
conn.Open ( ) ;
// Create the SQL command that links to a stored procedure
OleDbCommand cmd = new OleDbCommand ( "Categories" , conn ) ;
// Set the type to TableDirect
// Construct the data reader
using ( OleDbDataReader reader = cmd.ExecuteReader ( ) )
{
Console.WriteLine ( "" ) ;
Console.WriteLine ( "*** OleDbProvider ***" ) ;
//Console.WriteLine ( "Listing all records in Categories table..." ) ;
Console.WriteLine ( ) ;
Console.WriteLine ( "ID Name Description" ) ;
Console.WriteLine ( "-----------------------------------------------------" ) ;
// Iterate through the data
while ( reader.Read ( ) )
{
Console.WriteLine ( "{0,-3} {1,-15} {2}---此段代码没有执行" , reader.GetString(0) , reader[1], reader[2] ) ;
}
reader.Close ( ) ;
Console.WriteLine ( ) ;
}
// Close the connection
conn.Close ( ) ;
}
}
protected static void ExecuteBatch ( string source )
{
string select = "SELECT count(*) FROM student;";
//SELECT COUNT(*) FROM Products
// Connect to the database...
using ( SqlConnection conn = new SqlConnection(source) )
{
// Open the database connection
conn.Open ( ) ;
// Create the SQL command...
SqlCommand cmd = new SqlCommand ( select , conn ) ;
// Construct the data reader
using ( SqlDataReader reader = cmd.ExecuteReader ( ) )
{
// Output headings...
Console.WriteLine ( "*** SqlProvider ***" ) ;
Console.WriteLine ( "Output from batched SQL statements" ) ;
Console.WriteLine ( ) ;
int statement = 0 ;
do
{
statement++ ;
while ( reader.Read ( ) )
{
Console.WriteLine ( "Output from batch statement {0} is {1}" , statement , reader[0]) ;
}
} while ( reader.NextResult ( ) ) ;
reader.Close ( ) ;
}
conn.Close ( ) ;
}
}
protected static void ExecuteXml ( string source )
{
string select = "SELECT NO,id,Nname,score FROM student FOR XML AUTO";
using ( SqlConnection conn = new SqlConnection(source) )
{
// Open the database connection
conn.Open ( ) ;
// Create the SQL command...
SqlCommand cmd = new SqlCommand ( select , conn ) ;
// Construct an Xml Reader
XmlReader xr = cmd.ExecuteXmlReader ( ) ;
Console.WriteLine ( "" ) ;
Console.WriteLine ( "*** SqlProvider ***" ) ;
Console.WriteLine ( "Use ExecuteXmlReader with a FOR XML AUTO SQL clause" ) ;
Console.WriteLine ( ) ;
StreamWriter swd;
swd = File.CreateText("oscar.txt");
//Do something useful with the xml
//while ( xr.Read() )
//{
// string s = xr.ReadOuterXml();
// Console.WriteLine ( s) ;
// //File.AppendAllText("oscar.txt",xr.ReadOuterXml());
//此种方式不能全部打印console.WriteLine，在while（xr.read())也会执行向下一次。
//}
xr.Read();
string s1="0";
do
{
s1 = xr.ReadOuterXml();
swd.WriteLine(s1);
if (s1 != "")
Console.WriteLine(s1+DateTime.Now);
} while (s1 != "");
swd.Close();
// And close the connection
conn.Close ( ) ;
}
}
protected static void Dateset1(string source)
{
SqlConnection conn = new SqlConnection(source);
conn.Open();
string select = "select * from student";
//SqlCommand cmd = new SqlCommand(select, conn);
//SqlDataAdapter sda = new SqlDataAdapter(cmd);
SqlDataAdapter sda = new SqlDataAdapter(select, conn);
DataSet ds = new DataSet();
//填充数据到coustomers表
sda.Fill(ds, "Customers");
//向表添加一行数据
DataRow dr = ds.Tables["Customers"].NewRow();
dr["id"] = 99;
dr["Nname"] = "samon";
dr["score"] = 88;
ds.Tables["Customers"].Rows.Add(dr);
//DataRow dr = ds.Tables["Customers"].Rows.Add(new object[] { 4, "samon", 88 });
foreach (DataRow row in ds.Tables["Customers"].Rows)
Console.WriteLine("{0} {1,-30},{2,-16},{3},{4}",
row[0], row[1],row[2],row[3],row.RowState);
SqlCommand cmd = new SqlCommand("myprc", conn);
sda.InsertCommand = cmd;
// Set the type to stored procedure
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter score = cmd.Parameters.Add(new SqlParameter("@score", SqlDbType.Int));
SqlParameter usename = cmd.Parameters.Add(new SqlParameter("@usename", SqlDbType.NVarChar, 50));
SqlParameter countN = cmd.Parameters.Add(new SqlParameter("@countN", SqlDbType.Int));
score.SourceColumn = "score";
usename.SourceColumn = "Nname";
countN.Value = 3;
sda.Update(ds, "Customers");
sda.Fill(ds, "Customers1");
foreach (DataRow row in ds.Tables["Customers1"].Rows)
Console.WriteLine("{0} {1,-30},{2,-16},{3},{4}",
row[0], row[1], row[2], row[3], row.RowState);
//从数据库中加载数据到手动创建的表，如果列名不匹配，会自动添加列
DataTable products = new DataTable("products");
products.Columns.Add(new DataColumn("NO", typeof(Int16)));
products.Columns.Add(new DataColumn("ID", typeof(string)));
products.Columns.Add(new DataColumn("N0name", typeof(string)));
products.Columns.Add(new DataColumn("score", typeof(int)));
ds.Tables.Add(products);
sda.Fill(ds, "products");
foreach (DataRow row in ds.Tables["products"].Rows)
Console.WriteLine("{0} {1,-30},{2,-16},{3}", row[0], row[1], row[4], row[3]);
ds.WriteXml(".\\test.xml");
ds.WriteXml(".\\taseta.xml", XmlWriteMode.WriteSchema);
conn.Close();
}
}
TAG:
标题搜索
日历
|
|||||||||
| 日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
| 1 | 2 | ||||||||
| 3 | 4 | 5 | 6 | 7 | 8 | 9 | |||
| 10 | 11 | 12 | 13 | 14 | 15 | 16 | |||
| 17 | 18 | 19 | 20 | 21 | 22 | 23 | |||
| 24 | 25 | 26 | 27 | 28 | 29 | 30 | |||
| 31 | |||||||||
我的存档
数据统计
- 访问量: 124878
- 日志数: 107
- 文件数: 1
- 建立时间: 2007-04-20
- 更新时间: 2017-12-29
