人生是一场不能存盘的RPG,我只能尽量多搞几个Screenshot

Using MADS to Execute multiple commands on a connecton.

Add MultipleActiveResultSets=true into the connection string.

在connection string中指定 Asynchronous Processing = true.
Sample:
string strConn = “Data Source=.; Database=Pubs; Integrated Security=true; Asynchronous Processing=true”;
using (SqlConnection cn = new SqlConnection(strConn))
{
using(SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = “”;
IAsyncResult ar = command.BeginExecuteReader();
// …
// do other processing
// …
SqlDataReader r = command.EndExecuteReader(ar);
}
}

参考
Asynchronous Command Execution in ADO.NET 2.0
http://msdn2.microsoft.com/en-us/library/ms379553(VS.80).aspx

When I try to use “New Query” in MS Sql Server Managemnet Studio, an error pop up:
Cannot find template file for the new query (’C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\SQL\SQLFile.sql’).

I check this file on other guy’s machine, and found this file is an empty sql file.
So, I create it on my machine and fix the error.

I think I mistakenly deleted it by a disk cleaning tool name fastcleaner because this file with 0 byte size.

When I create a data base in the VS2005, I get a error message box which
says: “An error occurred while processing the local data file:
Key not valid for use in specified state.” The .mdf was created successfully
but I can’t open it in VS2005.

Google tell me to delete the folder:
C:\Documents and Settings\<My Name>\Application Data\Microsoft\VisualStudio\8.0
I clean the folder, then I can use the data base as usual.

Column XXX is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
——————
http://sqlzoo.net/howto/source/z.dir/err979/sqlserver
When using a GROUP BY clause every field in the SELECT list must be either:
One of the GROUP BY terms - in this case region
An aggregate function - for example SUM or COUNT
An expression based on the above

In the example the field name may not be used on the SELECT line.
SELECT name, region, MAX(population)
FROM bbc
GROUP BY region

In a group by line each region shows up only once - however in a typical region such
as Africa there are several different name values. WHich one should SQL pick?

Solutions
-Remove the offending field from the SELECT line
-Add the field to the GROUP BY clause
-Aggregate the offending field

Specific to SQL Server

方法1.执行 select @@version
SQL Server 2005 RTM版本为9.00.1399.06
SQL Server 2005 SP1版本为9.00.2047.00 (我目前的版本)
SQL Server 2005积累补丁版本为9.00.2153.00

方法2.执行
SELECT SERVERPROPERTY(’productversion’), SERVERPROPERTY (’productlevel’), SERVERPROPERTY (’edition’)
见http://support.microsoft.com/kb/321185/zh-cn

我的代码

string connstr= @\"Data Source=.\sql2k5;Initial Catalog=SQLStudy; Integrated Security=SSPI\";
SqlConnection conn = null;
SqlTransaction trans = null;
try
{
        conn = new SqlConnection(connstr);
        conn.Open();
        trans = conn.BeginTransaction();
        string sql = \"Insert into Student values(100, 'ak47')\";
        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        //new SqlCommand(sql, trans.Connection);会报错!
        SqlCommand command = new SqlCommand(sql, trans.Connection, trans);
        command.ExecuteNonQuery();
        trans.Commit();
}
catch (Exception exp)
{
        Console.WriteLine(exp.Message);
        if(trans != null)
        {
                trans.Rollback();
        }
}
finally
{
        if(conn != null)
        {
                conn.Close();
        }
        if(trans != null)
        {
                trans.Dispose();
        }
}

MSDN

using (SqlConnection conn = new SqlConnection(dbConString))
{
   conn.Open();
   using (SqlTransaction trans = conn.BeginTransaction())
   {
      try
      {
         ...
              trans.Commit();
      }
      catch
      {
         trans.Rollback();
      }
   }
}
	

By Riven Huang 2006.07.26
参照
Why I Don’t Use DataSets in My ASP.NET Applications(by Scott Michell)
http://aspnet.4guysfromrolla.com/articles/050405-1.aspx

Performance Comparison: Data Access Techniques
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch031.asp

首先需要理解DataSet 和 DataReader的设计目标:
DataSet可以理解成一个小型的,存在于内存中的数据库,包含多个data table,table之间
可存在约束关系.
DataSet和数据库无关,由DataAdapter来负责对数据库的处理,一旦数据填充结束,就和数据库
断开连接.
DataSet对XML的支持比较好.

DataReader可以理解成程序和数据库之间的桥梁.只能顺序的,从数据库中读取记录.
DataReader是和数据库相关的,所以存在sql, ole等多个版本的DataReader.

从使用上来看,

使用DataReader需要以下步骤:
// 1. 建立连接
SqlConnection myConnection = new SqlConnection(conn);

// 2. 执行查询
SqlCommand myCommand = new SqlCommand(myConnection, sqlText);

SqlReader myReader = myCommand.ExecuteReader();

// 3. Read
while(myReader.Read())
{

}

// 4. Close connection
myConnection.Close();

使用DataSet需要以下步骤:
// 1. 建立连接
SqlConnection myConnection = new SqlConnection(conn);

// 2. 生成command 和 adapter
SqlCommand myCommand = new SqlCommand(myConnection, sqlText);
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

// 3. 生成dataset并填充
DataSet myDataSet = new DataSet();
myAdapter.Fill(myDataSet);

// 4. Close connection
myConnection.Close();

如果使用UIControlo显示数据,对于dataset和datareader的操作是相同的:
把dataset或datareader赋给control的DataSource属性,
再调用control的DataBind()方法.

比较:
DataSet的性能比DataReader差很对,同时占用大量的内存.

何时使用DataSet:
1.数据传输.
2.桌面应用.

–dataTable可以独立于dataset而存在,
–dataTable可从datareader中读取数据:

using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Create a Command
using (SqlCommand command = new SqlCommand(Sql, connection))
{
// Call ExecuteReader to return a DataReader
using (SqlDataReader reader = command.ExecuteReader())
{
// Create a DataTable
DataTable table = new DataTable();
// Fill DataTable
table.Load(reader, LoadOption.OverwriteChanges);
// Display data in GridView
dataGridView1.DataSource = table;
}
}
}
–也可用Adapter填充

SqlDataAdapter adapter = new SqlDataAdapter(Sql, connection);
DataTable table = new DataTable(”Employees”);
adapter.Fill(table);

–用DataTableReaderCreate(it is disconnected)从datatable中读取数据:
DataTableReader dtReader = table.CreateDataReader();
while (dtReader.Read())
{
str = dtReader.GetValue(0).ToString();
}

如果
DataTableReader dtReader = dataset.CreateDataReader();
dtRader会读取DataSet中所有DataTable的数据.

–可以merge多个data table

dtTable1.Merge(dtTable2);

–Serialize的支持,从而可以在web method中传递DataTable
DataTable table = new DataTable();

table.Load(reader, LoadOption.OverwriteChanges);
// 设置序列化的格式,default为SerializationFormat.Xml
table.RemotingFormat = SerializationFormat.Binary;

BinaryFormatter bf = new BinaryFormatter();
FileStream fs = new FileStream(”Data.txt”, FileMode.OpenOrCreate);
bf.Serialize(fs, table);

如果在生成SQL Parameter时不指定类型会如何?

<appSettings>
<add key=”ConnectionString” value=”Server=.\SQL2k5;Database=Northwind;Integrated Security=SSPI” />
</appSettings>

string strConn = ConfigurationSettings.AppSettings[”ConnectionString”];
using (SqlConnection connection = new SqlConnection(strConn))
{
connection.Open();
string orderId = “10248′”;

string sql = “SELECT * FROM ORDERS WHERE OrderId= @OrderId”;
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add(new SqlParameter(”@OrderId” ,orderId) ); //非法的字符串
try
{
command.ExecuteNonQuery();
}
catch (Exception exp)
{
Console.WriteLine(exp.Message);
}
}

上面的例子中, OrderId在数据库中的类型为int, 我在生成parameter时故意
传入一个包含单引号的字符串,运行时会报错:
Conversion failed when converting the nvarchar value ‘10248′’ to data type int.

看来MS会进行数据类型检查.

SqlCommand Parameter And "Like" 条件

为了生成这样一个条SQL语句
SqlCommand cmd = new SqlCommand("SELECT * FROM People WHERE MENU LIKE ‘%包子%’");

我使用了如下的代码:
SqlCommand cmd = new SqlCommand("SELECT * FROM People WHERE MENU LIKE %@Name%");
cmd.Parameters.Add("@Name", SqlDbType.NVarChar);
cmd.Parameters["@Name"].Value = "包子";
执行时报错.

进而改为
SqlCommand cmd = new SqlCommand("SELECT * FROM People WHERE MENU LIKE ‘%@Name%’");
cmd.Parameters.Add("@Name", SqlDbType.NVarChar);
cmd.Parameters["@Name"].Value = "包子";
可执行.
改成
SqlCommand cmd = new SqlCommand("SELECT * FROM People WHERE MENU LIKE ‘%’ + @Name + ‘%’");
也可以干活,从而可以看出MS的做法是在字符类型的@Name参数前后加单引号,而不管%

但我实在不想在代码中出现单引号这种讨厌的东西.
后改为:
SqlCommand cmd = new SqlCommand("SELECT * FROM People WHERE MENU LIKE @Name");
cmd.Parameters.Add("@Name", SqlDbType.NVarChar);
cmd.Parameters["@Name"].Value = "%包子%";

有两张表:
Student: stdudentid (int), name(string)
Scroe: stdudentid (int), score
Score表中会有多个老师给学生打的分数,

现在我想查出所有的学生,以及他们的平均分数,该如何写sql语句?
我瞎搞了一个
Select st.Name, AVG(sc.Score) from student as st join Score as sc on st. stdudentid = st. stdudentid

高手答曰:

Select st.studentid, st.name, AVG (sc.Score) from student as st inner join Score as sc on st. stdudentid = sc. stdudentid Group by st. stdudentid, st.name
我怎么没想到呢?

考虑有的学生没有分数,改进为:

Select st.studentid, st.name, AVG (IsNull(sc.Score,0)) from student as st LEFT JOIN Score as sc on st. stdudentid = sc. stdudentid Group by st. stdudentid, st.name

//—工具:
0. Sql Profiler 和Index Tuning

1. 使用SQL server Management Studio(sql2005)的Management节点下的Active Monitor
或 Enterprise Manager(sql2000)的Management节点下的Current Active
可以查看 进程信息, lock信息

2. 上述节点下的 SQL Server logs

3. 系统存储过程,如 sp_who, sp_monitor,等.
可以在程序中使用这些sp来监视sql server

4. 内置函数

5. Query Analyzer中的 Show Server Trace选项

6. WMI SQL Server Administration Provider

//—用Sql Profiler来查看sql 语句的执行:
Events 选 TSQL
Column 选 TextData

Column Filter中Application Name 的Like设置为 “<app name>”

参考
SQL Server 2000 Performance Tuning Tools
http://www.sqlteam.com/item.asp?ItemID=761

Tips for Using SQL Server Query Analyzer
http://www.sql-server-performance.com/query_analyzer_tips.asp

在程序中用到以下的机制来新建一个account 库
1 datach account 标准库,
2 copy account 标准库的mdf文件,生成新的account库mdf文件.
3 attach 新的account库.

但是这个过程的步骤2会在sql2005上出错.
可用以下的sql语句来重现这个错误

1 create database AA
2 exec sp_detach_db AA,’True’
3 exec xp_cmdshell ‘copy /Y "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AA.mdf" "D:\AA.mdf"’

语句1执行后会生成一个AA.mdf文件,在查看其属性中的Sercurity页,会看到以下帐号
Administrator
Network service
Service

sql2000中当语句2执行后查看AA.mdf文件属性中的Sercurity页,会看到以下帐号
Administrator
SQLServer2005MSSQLUser$机器名$实例名
Service

而在sql2005中当语句2执行后查看AA.mdf文件属性中的Sercurity页,只会看到当前操作者的帐号

语句3中的xp_cmdshell是以Service权限执行的,所以在Sql2005下会失败.
提示Access is denined.

同时,如果sqlserver中已经禁止掉xp_cmdshell的使用,会看到以下的错误提示:
SQL Server blocked access to procedure ’sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this
component is turned off as part of the security configuration for this server.
A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure.
For more information about enabling ‘xp_cmdshell’, see "Surface Area Configuration" in SQL Server Books Online.

附:
http://msdn2.microsoft.com/zh-cn/library/ms190693(SQL.90).aspx

sql2005默认情况下，xp_cmdshell 安装时处于禁用状态，但是可以通过使用
外围应用配置器工具(Configuration Tools->SQL Server Surface Area Configuration)
或运行 sp_configure 系统存储过程来启用它，代码如下：

– To allow advanced options to be changed.
EXEC sp_configure ’show advanced options’, 1
GO
– To update the currently configured value for advanced options.
RECONFIGURE
GO
– To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
– To update the currently configured value for this feature.
RECONFIGURE
GO

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N’[dbo].[_MyTable]’) AND type in (N’U'))
或
IF not exists (select * from dbo.sysobjects where id = OBJECT_ID(N’[dbo].[_MyTable]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
DROP TABLE [dbo].[_MyTable]
GO

CREATE TABLE [dbo].[_MyTable]
(
 [Test] [varchar](32)
)
GO

string connectionString = "";
string queryString = ""; 

//–使用dataset         
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        connection.Open();
        SqlCommand command = new SqlCommand(queryString, connection);
        SqlDataAdapter da = new SqlDataAdapter(command);
        da.Fill(ds);
    }
    catch (Exception exp)
    {
        Console.WriteLine(exp.Message);
    }
}

//–使用Data reader
List<MyType> fields = new List<MyType>();

using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        connection.Open();
        SqlCommand command = new SqlCommand(queryString, connection);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            fields.Add(new MyType(name, type));
        }
    }
    catch (Exception exp)
    {
        Console.WriteLine(exp.Message);
    }
}

1.要注意from, where 附近的空格!

stirng sql = "SELECT COLUMN_NAME AS Name, " +
             "DATA_TYPE AS Type, " +
             "CHARACTER_MAXIMUM_LENGTH as Length " +
             "From INFORMATION_SCHEMA.COLUMNS " +
             "WHERE TABLE_NAME = ‘{0}’";

先google了一下,找到了两篇文章.

Schema: How do I show all the primary keys in a database? http://www.aspfaq.com/show.asp?id=2244

Querying System Tables http://www.sqlservercentral.com/columnists/rVasant/queryingsystemtables.asp 

下面是用到的一些语句:

–To retrieve all the user created tables:
SELECT name FROM sysobjects WHERE xtype = U

–To get list of all the stored procedures:
SELECT name FROM sysobjects WHERE xtype=P

–To retrieve all the fields of table ‘XYZ’:
SELECT name FROM syscolumns WHERE ID = (SELECT id FROM sysobjects WHERE name=’XYZ’)

下面是一些更高级些的,会用到 INFORMATION_SCHEMA views(sql2000以上),查看sql server的online help会看到更多的view

–得到Customers表上的所有的主键的name, type, 长度, default value

SELECT K.COLUMN_NAME,
           C.DATA_TYPE,
           C.CHARACTER_MAXIMUM_LENGTH,
           C.COLUMN_DEFAULT 
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T 
    OIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
     ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME 
   JOIN INFORMATION_SCHEMA.COLUMNS C
     ON C.Table_Name = T.TABLE_NAME
    AND C.COLUMN_NAME = K.COLUMN_NAME
WHERE T.CONSTRAINT_TYPE = ‘PRIMARY KEY’  AND T.TABLE_NAME = ‘Customers’

此外,How do I show all the primary keys in a database一文中的SQL的缩进格式也很值得学习

SELECT 
    T.TABLE_NAME, 
    T.CONSTRAINT_NAME, 
    K.COLUMN_NAME, 
    K.ORDINAL_POSITION 
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
    INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
    ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME 
WHERE
    T.CONSTRAINT_TYPE = ‘PRIMARY KEY’ 
    — AND T.TABLE_NAME = ‘table_name’
ORDER BY
    T.TABLE_NAME,
    K.ORDINAL_POSITION

//——————————————-

    config 文件中的connection stirng

//——————————————-

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="MyDB" connectionString="Data Source=myServer;Initial Catalog=myDB;Integrated Security=True"                                            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

//——————————————-

    读取

//——————————————-

    string conn = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;