如果这是一个控制台应用程序的话,应该在 应用程序 下面,添加新建项,然后添加一个应用程序配置配置文件(默认的名字就是App.config),然后在这里里面配置连接
<?xmlversion="1.0"encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name= "ConnStr" connectionString ="data source=FFB-VAIO; initial catalog=DBPromary; integrated security=true"/>
<addname="sqlserver" connectionString ="Data Source=FFB-VAIO; Initial Catalog=sales; PersistSecurity Info=True; User ID=sa;Password=123456; Pooling=true; MAX PoolSize=512; Min Pool Size=50; Connection Lifetime=30" providerName = "System.Data.SqlClient" />
</connectionStrings>
</configuration>
public void GetConnectionString()
{
//ConfigurationManager类需要引用,选中项目名称后点右键-添加引用,在.NET下选中System.Configuration双击就可以了,然后using一下就可以了:using System.Configuration;
stringgetCon = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
using(SqlConnection conn =newSqlConnection(getCon))
{
}
}
App.config文件配置
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="getConn" connectionString="data source=FFB-VAIO;initial catalog=sales;integrated security=true"/>
</connectionStrings>
</configuration>
Program.cs里面的代码
最原始版
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
namespace ADO.NET最新
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("请输入要插入的用户名:");
string userName=Console.ReadLine();
Console.WriteLine("请输入要插入的密码:");
string userPaw = Console.ReadLine();
string connStr = ConfigurationManager.ConnectionStrings["getConn"].ConnectionString;
//我们为什么要用using(){}。面试的时候也会问道Close,与Dispose的区别。Close:关闭后还能打开。Dispose:直接销毁,不能再次使用。using在出了作用域以后,也就是using语句在结束时会自动调用欲被清除对象的Dispose()方法。SqlConnection,SqlCommand,SqlDataReader,FileStream等的Disponse内部会先做这样的判断:判断有没有Close,如果没有就先Close在Disponse。也就是说Disponse内部会自动调用Close。所以用using(){}不用写Close(),
using(SqlConnection conn=new SqlConnection (connStr))
{
conn.Open();
//**************************************************ExecuteNonQuery()
using (SqlCommand cmd = conn.CreateCommand())
{
//cmd.CommandText = "insert into usertable values('马云',pass123)";
cmd.CommandText = "insert into usertable values('"+userName+"','"+userPaw+"')";
cmd.ExecuteNonQuery();//该方法主要用于除了Select查询以外的SQL命令,如Update,Insert Delete。该方法执行的命令不返回任何行。但可以返回执行命令时所影响到的行数,所以它的返回值是int类型
Console.WriteLine("数据插入成功");
}
//**************************************************ExecuteScalar()
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select count(*) from usertable";
Console.WriteLine(cmd.ExecuteScalar()); //sqlCommand的ExecuteScalar()方法用于执行查询,并返回查询所返回的结果值中的第一行的第一列(一般用于一些聚合函数的查询,比如查询汇总之类的,count,max min)
//一个表的主键id我们一般都是设为自增的所以在插入的数据的时候不需要写入值, 假如你插入一条数据,的时候你又想知道你插入的这条数据主键id是多少,这时候也可以用ExecuteScalar()来得到 output inserted.id 就是返回id的值。当然这里也可以是output inserted.userName,或者其他
cmd.CommandText = "insert into usertable(userName,userPaws) output inserted.id values('tom','789746')";
;
Console.WriteLine("插入项的id主键的值是{0}",cmd.ExecuteScalar());
}
//**************************************************ExecuteReader()
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from usertable";
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
//Console.WriteLine(dr.GetString(1) + dr.GetString(2));//也可以用下面的三种方法取值
Console.WriteLine(dr.GetString(dr.GetOrdinal("userName")) + dr.GetString(dr.GetOrdinal("userPaws")));//dr.GetOrdinal("userName")的意思就是获取userName列的序号,而dr.GetString()就是获取列名的值
//Console.WriteLine(dr["userName"].ToString() + dr["userPaws"].ToString());
//Console.WriteLine(dr[1].ToString() + dr[2].ToString());
}
}
}
}
Console.WriteLine("ok");
Console.ReadKey();
}
}
}
改进版
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
namespace ADO.NET最新
{
class Class1
{
public static string getConn() //获取数据库连接字符串。注:这个数据库连接字符串写在App.config文件里面。这里用这段代码读取出来。
{
string getConn = ConfigurationManager.ConnectionStrings["getConn"].ConnectionString;
return getConn;
}
//***********************************************ExecuteNonQuery()
public static void ExecuteNonQuery(string sql)
{
getConn();
using (SqlConnection conn = new SqlConnection(getConn()))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
Console.WriteLine("插入成功");
Console.ReadKey();
}
//***********************************************ExecuteScalar()
public static void ExecuteScalar(string sql)
{
getConn();
using (SqlConnection conn = new SqlConnection(getConn()))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
Console.WriteLine(cmd.ExecuteScalar());
}
}
Console.WriteLine("ok");
Console.ReadKey();
}
//***********************************************ExecuteReader()
public static void ExecuteReader(string sql)
{
getConn();
using (SqlConnection conn = new SqlConnection(getConn()))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Console.WriteLine("用户名:{0} 密码:{1}", dr.GetString(dr.GetOrdinal("userName")), dr.GetString(dr.GetOrdinal("userPaws"))); //或者以下三种形式都可以。
//Console.WriteLine("用户名:{0} 密码:{1}", dr["userName"].ToString(), dr["userPaws"].ToString());
//Console.WriteLine("用户名:{0} 密码:{1}", dr.GetString(1), dr.GetString(2));
//Console.WriteLine("用户名:{0} 密码:{1}", dr[1], dr[2]);
}
}
}
Console.WriteLine("ok");
Console.ReadKey();
}
}
}
class Program
{
static void Main(string[] args)
{
Class1.ExecuteNonQuery("insert into usertable values('胡锦涛',456789)");
Class1.ExecuteScalar("select count(*) from usertable");
Class1.ExecuteReader("select * from usertable");
}
}
}