1.ConnectionString,数据库链接
Web.config
<configuration>
<connectionStrings>
<!-- 数据库 SQL Server -->
<add name="ConnectionString" connectionString="Data Source=**IP地址**;Initial Catalog=**数据库名**;User ID=**用户名**;Password=**密码**;Min Pool Size=10;Max Pool Size=255;Connect Timeout=30" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
2.引入jjq_Model.tt即T4模板
注意更换Namespace和数据库对应表名
<#@ template hostspecific="True" #>
<#@ assembly name="EnvDTE" #>
<#@ assembly name="System.Core.dll" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Data.Entity.Design" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Configuration" #>
<#@ assembly name="System.Windows.Forms" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Data.Common" #>
<#@ import namespace="System.Diagnostics" #>
<#@ import namespace="System.Globalization" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ import namespace="System.Configuration" #>
<#@ import namespace="System.Windows.Forms" #>
<#
/*
This code is part of the Dapper.SimpleCRUD project
It is based on the T4 template from the PetaPoco project which in turn is based on the subsonic project.
-----------------------------------------------------------------------------------------
This template can read minimal schema information from the following databases:
* SQL Server
-----------------------------------------------------------------------------------------
*/
// Settings
ConnectionStringName = "ConnectionString"; // Uses last connection string in config if not specified
ConfigPath = @""; //Looks in current project for web.config or app.config by default. This overrides to a relative path - useful for seperate class library projects.
Namespace = "T4_Practice2";
ClassPrefix = "";
ClassSuffix = "";
IncludeViews = true;
IncludeRelationships = false;
ExcludeTablePrefixes = new string[]{ "JJQ_Practice"};
// Read schema
var tables = LoadTables();
/*
// Tweak Schema
tables["tablename"].Ignore = true; // To ignore a table
tables["tablename"].ClassName = "newname"; // To change the class name of a table
tables["tablename"]["columnname"].Ignore = true; // To ignore a column
tables["tablename"]["columnname"].PropertyName="newname"; // To change the property name of a column
tables["tablename"]["columnname"].PropertyType="bool"; // To change the property type of a column
*/
#>
using System;
using System.Linq;
using Dapper;
using System.Collections.Generic;
using System.Collections;
namespace <#=Namespace #>
{
<#
foreach(Table tbl in from t in tables where !t.Ignore select t){
if(IsExcluded(tbl.Name, ExcludeTablePrefixes)) continue;
#>
/// <summary>
/// <#=tbl.Name#>表
/// </summary>
[Table("<#=tbl.Name#>")]
public partial class <#=tbl.ClassName#>
{
public string tblName="<#=tbl.Name#>";
<#foreach(Column col in from c in tbl.Columns where !c.Ignore select c)
{#>
<#
var delVal=col.DefaultSetting.Replace("(","").Replace(")","").Replace("'","\"");
if (delVal=="")
{
if (col.PropertyType=="string")
{
delVal="=\"\"";
}
}else{
if (delVal=="getdate")
{
delVal="System.DateTime.Now";
}else if(delVal=="newid")
{
delVal="Guid.NewGuid()";
}
delVal="="+delVal;
}
#>
<#=col.PropertyType #><#=CheckNullable(col)#> _<#=col.PropertyName #><#=delVal#>;
/// <summary>
/// <#= col.PropertyName #>
/// </summary>
<# if (tbl.PK!=null && tbl.PK.Name==col.PropertyName) { #>
[Key]
<#}#>
<# if (col.IsAutoIncrement) { #>
[Identity]
<#}#>
public virtual <#=col.PropertyType #><#=CheckNullable(col)#> <#=col.PropertyName #> {
get { return _<#=col.PropertyName #>; }
set
{
if(_<#=col.PropertyName #>!=value){
_<#=col.PropertyName #>=value;
if(!_dirtyColumns.Contains("<#=col.Name #>") && _isLoaded){
_dirtyColumns.Add("<#=col.Name #>");
}
}
}
}
<#}#>
<# if (IncludeRelationships) { #>
<#foreach(Key key in from k in tbl.OuterKeys select k)
{#>
public virtual <#=tables[key.ReferencedTableName].ClassName #> <#=tables[key.ReferencedTableName].ClassName #> { get; set; }
<#}#>
<#foreach(Key key in from k in tbl.InnerKeys select k)
{#>
public virtual IEnumerable<<#=tables[key.ReferencingTableName].ClassName #>> <#=tables[key.ReferencingTableName].CleanName #> { get; set; }
<#}#>
<#}#>
bool _isNew=true;
public bool IsNew(){
return _isNew;
}
public void SetIsNew(bool isNew){
_isNew=isNew;
}
bool _isLoaded;
public bool IsLoaded(){
return _isLoaded;
}
public void SetIsLoaded(bool isLoaded){
_isLoaded=isLoaded;
_isNew= false;
}
ArrayList _dirtyColumns=new ArrayList();
public bool IsDirty(){
return _dirtyColumns.Count>0;
}
public ArrayList GetDirtyColumns (){
return _dirtyColumns;
}
public <#=tbl.PK.PropertyType #> Save() {
using (var conn = DBManage.OpenConnection())
{
<#=tbl.PK.PropertyType #> ret = conn.Insert<<#=tbl.PK.PropertyType #>>(this);
return ret;
}
}
public <#=tbl.PK.PropertyType #> Update()
{
using (var conn = DBManage.OpenConnection())
{
if (_dirtyColumns.Count>0)
{
<#=tbl.PK.PropertyType #> ret = conn.Update(this);
return ret;
}else{
return 0;
}
}
}
public int SaveOrUpdate()
{
int dataid = 0;
if (_isNew)
{
dataid = Save();
return dataid;
}
else {
dataid = Update();
return dataid;
}
}
public void Delete()
{
using (var conn = DBManage.OpenConnection())
{
conn.Delete(this);
}
}
public static void Delete<<#=tbl.ClassName#>>(object id)
{
using (var conn = DBManage.OpenConnection())
{
conn.Delete<<#=tbl.ClassName#>>(id);
}
}
public static void Delete(string whereConditions)
{
using (var conn = DBManage.OpenConnection())
{
conn.Delete<<#=tbl.ClassName#>>(whereConditions);
}
}
public static void Delete(object whereConditions)
{
using (var conn = DBManage.OpenConnection())
{
conn.DeleteList<<#=tbl.ClassName#>>(whereConditions);
}
}
public static <#=tbl.ClassName#> SingleOrDefault(string whereConditions) {
using (var conn = DBManage.OpenConnection())
{
var results=conn.GetList<<#=tbl.ClassName#>>(whereConditions);
<#=tbl.ClassName#> single=null;
if(results.Count() > 0){
single=results.First();
single.SetIsLoaded(true);
single.SetIsNew(false);
}
return single;
}
}
public static <#=tbl.ClassName#> SingleOrDefault(object whereConditions) {
using (var conn = DBManage.OpenConnection())
{
var results=conn.GetList<<#=tbl.ClassName#>>(whereConditions);
<#=tbl.ClassName#> single=null;
if(results.Count() > 0){
single=results.First();
single.SetIsLoaded(true);
single.SetIsNew(false);
}
return single;
}
}
public static IEnumerable<<#=tbl.ClassName#>> GetList(object whereConditions)
{
using (var conn = DBManage.OpenConnection())
{
return conn.GetList<<#=tbl.ClassName#>>(whereConditions);
}
}
public static IEnumerable<<#=tbl.ClassName#>> GetList()
{
using (var conn = DBManage.OpenConnection())
{
return conn.GetList<<#=tbl.ClassName#>>();
}
}
public static IEnumerable<<#=tbl.ClassName#>> GetList(string Conditions)
{
using (var conn = DBManage.OpenConnection())
{
return conn.GetList<<#=tbl.ClassName#>>(Conditions);
}
}
}
<#}#>
}
<#+
/*
The contents of this file are subject to the New BSD
License (the "License"); you may not use this file
except in compliance with the License. You may obtain a copy of
the License at http://www.opensource.org/licenses/bsd-license.php
Software distributed under the License is distributed on an
"AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
implied. See the License for the specific language governing
rights and limitations under the License.
*/
string ConnectionStringName = "";
string ConfigPath = "";
string Namespace = "";
string ClassPrefix = "";
string ClassSuffix = "";
string SchemaName = null;
bool IncludeViews;
bool IncludeRelationships;
string[] ExcludeTablePrefixes = new string[]{};
public class Table
{
public List<Column> Columns;
public List<Key> InnerKeys = new List<Key>();
public List<Key> OuterKeys = new List<Key>();
public string Name;
public string Schema;
public bool IsView;
public string CleanName;
public string ClassName;
public string SequenceName;
public bool Ignore;
public Column PK
{
get
{
return this.Columns.SingleOrDefault(x=>x.IsPK);
}
}
public Column GetColumn(string columnName)
{
return Columns.Single(x=>string.Compare(x.Name, columnName, true)==0);
}
public Column this[string columnName]
{
get
{
return GetColumn(columnName);
}
}
}
public class Column
{
public string Name;
public string PropertyName;
public string PropertyType;
public bool IsPK;
public bool IsNullable;
public bool IsAutoIncrement;
public bool Ignore;
public string DefaultSetting;
}
public class Key
{
public string Name;
public string ReferencedTableName;
public string ReferencedTableColumnName;
public string ReferencingTableName;
public string ReferencingTableColumnName;
}
public class Tables : List<Table>
{
public Tables()
{
}
public Table GetTable(string tableName)
{
return this.Single(x=>string.Compare(x.Name, tableName, true)==0);
}
public Table this[string tableName]
{
get
{
return GetTable(tableName);
}
}
}
static Regex rxCleanUp = new Regex(@"[^\w\d_]", RegexOptions.Compiled);
static Func<string, string> CleanUp = (str) =>
{
str = rxCleanUp.Replace(str, "_");
if (char.IsDigit(str[0])) str = "_" + str;
return str;
};
string CheckNullable(Column col)
{
string result="";
if(col.IsNullable &&
col.PropertyType !="byte[]" &&
col.PropertyType !="string" &&
col.PropertyType !="Microsoft.SqlServer.Types.SqlGeography" &&
col.PropertyType !="Microsoft.SqlServer.Types.SqlGeometry"
)
result="?";
return result;
}
string GetConnectionString(ref string connectionStringName, out string providerName)
{
var _CurrentProject = GetCurrentProject();
providerName=null;
string result="";
ExeConfigurationFileMap configFile = new ExeConfigurationFileMap();
configFile.ExeConfigFilename = GetConfigPath();
if (string.IsNullOrEmpty(configFile.ExeConfigFilename))
throw new ArgumentNullException("The project does not contain App.config or Web.config file.");
var config = System.Configuration.ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);
var connSection=config.ConnectionStrings;
//if the connectionString is empty - which is the defauls
//look for count-1 - this is the last connection string
//and takes into account AppServices and LocalSqlServer
if(string.IsNullOrEmpty(connectionStringName))
{
if(connSection.ConnectionStrings.Count>1)
{
connectionStringName = connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].Name;
result=connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].ConnectionString;
providerName=connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].ProviderName;
}
}
else
{
try
{
result=connSection.ConnectionStrings[connectionStringName].ConnectionString;
providerName=connSection.ConnectionStrings[connectionStringName].ProviderName;
}
catch
{
result="There is no connection string name called '"+connectionStringName+"'";
}
}
// if (String.IsNullOrEmpty(providerName))
// providerName="System.Data.SqlClient";
return result;
}
string _connectionString="";
string _providerName="";
void InitConnectionString()
{
if(String.IsNullOrEmpty(_connectionString))
{
_connectionString=GetConnectionString(ref ConnectionStringName, out _providerName);
if(_connectionString.Contains("|DataDirectory|"))
{
//have to replace it
string dataFilePath=GetDataDirectory();
_connectionString=_connectionString.Replace("|DataDirectory|",dataFilePath);
}
}
}
public string ConnectionString
{
get
{
InitConnectionString();
return _connectionString;
}
}
public string ProviderName
{
get
{
InitConnectionString();
return _providerName;
}
}
public EnvDTE.Project GetCurrentProject() {
IServiceProvider _ServiceProvider = (IServiceProvider)Host;
if (_ServiceProvider == null)
throw new Exception("Host property returned unexpected value (null)");
EnvDTE.DTE dte = (EnvDTE.DTE)_ServiceProvider.GetService(typeof(EnvDTE.DTE));
if (dte == null)
throw new Exception("Unable to retrieve EnvDTE.DTE");
Array activeSolutionProjects = (Array)dte.ActiveSolutionProjects;
if (activeSolutionProjects == null)
throw new Exception("DTE.ActiveSolutionProjects returned null");
EnvDTE.Project dteProject = (EnvDTE.Project)activeSolutionProjects.GetValue(0);
if (dteProject == null)
throw new Exception("DTE.ActiveSolutionProjects[0] returned null");
return dteProject;
}
private string GetProjectPath()
{
EnvDTE.Project project = GetCurrentProject();
System.IO.FileInfo info = new System.IO.FileInfo(project.FullName);
return info.Directory.FullName;
}
private string GetConfigPath()
{
if(ConfigPath !="")
return Host.ResolvePath(ConfigPath);
EnvDTE.Project project = GetCurrentProject();
foreach (EnvDTE.ProjectItem item in project.ProjectItems)
{
// if it is the app.config file, then open it up
if (item.Name.Equals("App.config",StringComparison.InvariantCultureIgnoreCase) || item.Name.Equals("Web.config",StringComparison.InvariantCultureIgnoreCase))
return GetProjectPath() + "\\" + item.Name;
}
return String.Empty;
}
public string GetDataDirectory()
{
EnvDTE.Project project=GetCurrentProject();
return System.IO.Path.GetDirectoryName(project.FileName)+"\\App_Data\\";
}
static string zap_password(string connectionString)
{
var rx = new Regex("Password=.*;", RegexOptions.Singleline | RegexOptions.Multiline | RegexOptions.IgnoreCase);
return rx.Replace(connectionString, "Password=******;");
}
static string Singularize(string word)
{
var singularword = System.Data.Entity.Design.PluralizationServices.PluralizationService.CreateService(System.Globalization.CultureInfo.GetCultureInfo("en-us")).Singularize(word);
return singularword;
}
static string RemoveTablePrefixes(string word)
{
var cleanword = word;
if(cleanword.StartsWith("tbl_")) cleanword = cleanword.Replace("tbl_","");
if(cleanword.StartsWith("tbl")) cleanword = cleanword.Replace("tbl","");
cleanword = cleanword.Replace("_","");
return cleanword;
}
static bool IsExcluded(string tablename, string[] ExcludeTablePrefixes)
{
for (int i = 0; i < ExcludeTablePrefixes.Length; i++)
{
string s = ExcludeTablePrefixes[i];
if(tablename.ToLower()==s.ToLower()) return false;
}
return true;
}
Tables LoadTables()
{
InitConnectionString();
WriteLine("// This file was automatically generated by the Dapper.SimpleCRUD T4 Template");
WriteLine("// Do not make changes directly to this file - edit the template instead");
WriteLine("// ");
WriteLine("// The following connection settings were used to generate this file");
WriteLine("// ");
WriteLine("// Connection String Name: `{0}`", ConnectionStringName);
WriteLine("// Provider: `{0}`", ProviderName);
WriteLine("// Connection String: `{0}`", zap_password(ConnectionString));
WriteLine("// Include Views: `{0}`", IncludeViews);
WriteLine("");
DbProviderFactory _factory;
try
{
_factory = DbProviderFactories.GetFactory(ProviderName);
}
catch (Exception x)
{
var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
Warning(string.Format("Failed to load provider `{0}` - {1}", ProviderName, error));
WriteLine("");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("// Failed to load provider `{0}` - {1}", ProviderName, error);
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("");
return new Tables();
}
try
{
Tables result;
using(var conn=_factory.CreateConnection())
{
conn.ConnectionString=ConnectionString;
conn.Open();
SchemaReader reader=null;
// Assume SQL Server
reader=new SqlServerSchemaReader();
reader.outer=this;
result=reader.ReadSchema(conn, _factory);
// Remove unrequired tables/views
for (int i=result.Count-1; i>=0; i--)
{
if (SchemaName!=null && string.Compare(result[i].Schema, SchemaName, true)!=0)
{
result.RemoveAt(i);
continue;
}
if (!IncludeViews && result[i].IsView)
{
result.RemoveAt(i);
continue;
}
}
conn.Close();
var rxClean = new Regex("^(Equals|GetHashCode|GetType|ToString|repo|Save|IsNew|Insert|Update|Delete|Exists|SingleOrDefault|Single|First|FirstOrDefault|Fetch|Page|Query)$");
foreach (var t in result)
{
t.ClassName = ClassPrefix + t.ClassName + ClassSuffix;
foreach (var c in t.Columns)
{
c.PropertyName = rxClean.Replace(c.PropertyName, "_$1");
// Make sure property name doesn't clash with class name
if (c.PropertyName == t.ClassName)
c.PropertyName = "_" + c.PropertyName;
}
}
return result;
}
}
catch (Exception x)
{
var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
Warning(string.Format("Failed to read database schema - {0}", error));
WriteLine("");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("// Failed to read database schema - {0}", error);
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("");
return new Tables();
}
}
abstract class SchemaReader
{
public abstract Tables ReadSchema(DbConnection connection, DbProviderFactory factory);
public GeneratedTextTransformation outer;
public void WriteLine(string o)
{
outer.WriteLine(o);
}
}
class SqlServerSchemaReader : SchemaReader
{
// SchemaReader.ReadSchema
public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
{
var result=new Tables();
_connection=connection;
_factory=factory;
var cmd=_factory.CreateCommand();
cmd.Connection=connection;
cmd.CommandText=TABLE_SQL;
//pull the tables in a reader
using(cmd)
{
using (var rdr=cmd.ExecuteReader())
{
while(rdr.Read())
{
Table tbl=new Table();
tbl.Name=rdr["TABLE_NAME"].ToString();
tbl.Schema=rdr["TABLE_SCHEMA"].ToString();
tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
tbl.CleanName=CleanUp(tbl.Name);
if(tbl.CleanName.StartsWith("tbl_")) tbl.CleanName = tbl.CleanName.Replace("tbl_","");
if(tbl.CleanName.StartsWith("tbl")) tbl.CleanName = tbl.CleanName.Replace("tbl","");
tbl.CleanName = tbl.CleanName.Replace("_","");
tbl.ClassName=Singularize(RemoveTablePrefixes(tbl.CleanName));
result.Add(tbl);
}
}
}
foreach (var tbl in result)
{
tbl.Columns=LoadColumns(tbl);
// Mark the primary key
string PrimaryKey=GetPK(tbl.Name);
var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
if(pkColumn!=null)
{
pkColumn.IsPK=true;
}
try
{
tbl.OuterKeys = LoadOuterKeys(tbl);
tbl.InnerKeys = LoadInnerKeys(tbl);
}
catch (Exception x)
{
var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
WriteLine("");
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine(String.Format("// Failed to get relationships for `{0}` - {1}", tbl.Name, error));
WriteLine("// -----------------------------------------------------------------------------------------");
WriteLine("");
}
}
return result;
}
DbConnection _connection;
DbProviderFactory _factory;
List<Column> LoadColumns(Table tbl)
{
using (var cmd=_factory.CreateCommand())
{
cmd.Connection=_connection;
cmd.CommandText=COLUMN_SQL;
var p = cmd.CreateParameter();
p.ParameterName = "@tableName";
p.Value=tbl.Name;
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "@schemaName";
p.Value=tbl.Schema;
cmd.Parameters.Add(p);
var result=new List<Column>();
using (IDataReader rdr=cmd.ExecuteReader())
{
while(rdr.Read())
{
Column col=new Column();
col.Name=rdr["ColumnName"].ToString();
col.PropertyName=CleanUp(col.Name);
col.PropertyType=GetPropertyType(rdr["DataType"].ToString());
col.IsNullable=rdr["IsNullable"].ToString()=="YES";
col.IsAutoIncrement=((int)rdr["IsIdentity"])==1;
col.DefaultSetting=rdr["DefaultSetting"].ToString();
result.Add(col);
}
}
return result;
}
}
List<Key> LoadOuterKeys(Table tbl)
{
using (var cmd=_factory.CreateCommand())
{
cmd.Connection=_connection;
cmd.CommandText=OUTER_KEYS_SQL;
var p = cmd.CreateParameter();
p.ParameterName = "@tableName";
p.Value=tbl.Name;
cmd.Parameters.Add(p);
var result=new List<Key>();
using (IDataReader rdr=cmd.ExecuteReader())
{
while(rdr.Read())
{
var key=new Key();
key.Name=rdr["FK"].ToString();
key.ReferencedTableName = rdr["Referenced_tbl"].ToString();
key.ReferencedTableColumnName = rdr["Referenced_col"].ToString();
key.ReferencingTableColumnName = rdr["Referencing_col"].ToString();
result.Add(key);
}
}
return result;
}
}
List<Key> LoadInnerKeys(Table tbl)
{
using (var cmd=_factory.CreateCommand())
{
cmd.Connection=_connection;
cmd.CommandText=INNER_KEYS_SQL;
var p = cmd.CreateParameter();
p.ParameterName = "@tableName";
p.Value=tbl.Name;
cmd.Parameters.Add(p);
var result=new List<Key>();
using (IDataReader rdr=cmd.ExecuteReader())
{
while(rdr.Read())
{
var key=new Key();
key.Name=rdr["FK"].ToString();
key.ReferencingTableName = rdr["Referencing_tbl"].ToString();
key.ReferencedTableColumnName = rdr["Referenced_col"].ToString();
key.ReferencingTableColumnName = rdr["Referencing_col"].ToString();
result.Add(key);
}
}
return result;
}
}
string GetPK(string table){
string sql=@"SELECT c.name AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.objects AS o ON i.object_id = o.object_id
LEFT OUTER JOIN sys.columns AS c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE (i.type = 1) AND (o.name = @tableName)";
using (var cmd=_factory.CreateCommand())
{
cmd.Connection=_connection;
cmd.CommandText=sql;
var p = cmd.CreateParameter();
p.ParameterName = "@tableName";
p.Value=table;
cmd.Parameters.Add(p);
var result=cmd.ExecuteScalar();
if(result!=null)
return result.ToString();
}
return "";
}
string GetPropertyType(string sqlType)
{
string sysType="string";
switch (sqlType)
{
case "bigint":
sysType = "long";
break;
case "smallint":
sysType= "short";
break;
case "int":
sysType= "int";
break;
case "uniqueidentifier":
sysType= "Guid";
break;
case "smalldatetime":
case "datetime":
case "datetime2":
case "date":
case "time":
sysType= "DateTime";
break;
case "float":
sysType="double";
break;
case "real":
sysType="float";
break;
case "numeric":
case "smallmoney":
case "decimal":
case "money":
sysType= "decimal";
break;
case "tinyint":
sysType = "byte";
break;
case "bit":
sysType= "bool";
break;
case "image":
case "binary":
case "varbinary":
case "timestamp":
sysType= "byte[]";
break;
case "geography":
sysType = "Microsoft.SqlServer.Types.SqlGeography";
break;
case "geometry":
sysType = "Microsoft.SqlServer.Types.SqlGeometry";
break;
}
return sysType;
}
const string TABLE_SQL=@"SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW'";
const string COLUMN_SQL=@"SELECT
TABLE_CATALOG AS [Database],
TABLE_SCHEMA AS Owner,
TABLE_NAME AS TableName,
COLUMN_NAME AS ColumnName,
ORDINAL_POSITION AS OrdinalPosition,
COLUMN_DEFAULT AS DefaultSetting,
IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType,
CHARACTER_MAXIMUM_LENGTH AS MaxLength,
DATETIME_PRECISION AS DatePrecision,
COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@tableName AND TABLE_SCHEMA=@schemaName
ORDER BY OrdinalPosition ASC";
const string OUTER_KEYS_SQL = @"SELECT
FK = OBJECT_NAME(pt.constraint_object_id),
Referenced_tbl = OBJECT_NAME(pt.referenced_object_id),
Referencing_col = pc.name,
Referenced_col = rc.name
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
WHERE pt.parent_object_id = OBJECT_ID(@tableName);";
const string INNER_KEYS_SQL = @"SELECT
[Schema] = OBJECT_SCHEMA_NAME(pt.parent_object_id),
Referencing_tbl = OBJECT_NAME(pt.parent_object_id),
FK = OBJECT_NAME(pt.constraint_object_id),
Referencing_col = pc.name,
Referenced_col = rc.name
FROM sys.foreign_key_columns AS pt
INNER JOIN sys.columns AS pc
ON pt.parent_object_id = pc.[object_id]
AND pt.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON pt.referenced_column_id = rc.column_id
AND pt.referenced_object_id = rc.[object_id]
WHERE pt.referenced_object_id = OBJECT_ID(@tableName);";
}
#>
3.引入DBManage.cs+DbHelper.cs(注意引入需要更换Namespace)
DBManage
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using System.Data;
using System.Reflection;
namespace T4_Practice2
{
public class DBManage
{
public static SqlConnection OpenConnection(string connStr = "")
{
if (connStr == "")
{
connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
}
SqlConnection connection = new SqlConnection(connStr);
connection.Open();
if (connection.State != ConnectionState.Open)
connection.Open();
return connection;
}
public static void GetIListBySql(string sql, string connStr = "")
{
using (IDbConnection connection = OpenConnection(connStr))
{
connection.Execute(sql);
}
}
public static int RunSqlByScalar(string sql, string connStr = "")
{
using (SqlConnection connection = OpenConnection(connStr))
{
if (connection.State != ConnectionState.Open)
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
int i = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
return i;
}
}
public static DataTable ToDataTable(IEnumerable<dynamic> items)
{
var data = items.ToArray();
if (data.Count() == 0) return null;
var dt = new DataTable();
foreach (var key in ((IDictionary<string, object>)data[0]).Keys)
{
dt.Columns.Add(key);
}
foreach (var d in data)
{
dt.Rows.Add(((IDictionary<string, object>)d).Values.ToArray());
}
return dt;
}
public static DataTable LinqQueryToDataTable(IEnumerable<dynamic> v)
{
//We really want to know if there is any data at all
var firstRecord = v.FirstOrDefault();
if (firstRecord == null)
return null;
/*Okay, we have some data. Time to work.*/
//So dear record, what do you have?
PropertyInfo[] infos = firstRecord.GetType().GetProperties();
//Our table should have the columns to support the properties
DataTable table = new DataTable();
//Add, add, add the columns
foreach (var info in infos)
{
Type propType = info.PropertyType;
if (propType == typeof(DateTime?))
{
table.Columns.Add(info.Name, typeof(string));
}
else if (propType.IsGenericType
&& propType.GetGenericTypeDefinition() == typeof(Nullable<>)) //Nullable types should be handled too
{
table.Columns.Add(info.Name, Nullable.GetUnderlyingType(propType));
}
else
{
table.Columns.Add(info.Name, info.PropertyType);
}
}
//Hmm... we are done with the columns. Let's begin with rows now.
DataRow row;
foreach (var record in v)
{
row = table.NewRow();
for (int i = 0; i < table.Columns.Count; i++)
{
if (infos[i].PropertyType == typeof(DateTime?))
{
if (infos[i].GetValue(record) != null)
{
row[i] = Convert.ToDateTime(infos[i].GetValue(record)).ToString("yyyy-MM-dd");
}
else
{
row[i] = "";
}
}
else
{
row[i] = infos[i].GetValue(record) != null ? infos[i].GetValue(record) : DBNull.Value;
}
}
table.Rows.Add(row);
}
//Table is ready to serve.
table.AcceptChanges();
return table;
}
/// <summary>
/// sql语句查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static List<T> GetListBySql<T>(string sql)
{
using (IDbConnection conn = DBManage.OpenConnection())
{
IEnumerable<T> IElist = conn.Query<T>(sql).ToList();
List<T> tolist = new List<T>();
foreach (var item in IElist)
{
tolist.Add(item);
}
return tolist;
}
}
/// <summary>
/// sql语句查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static IEnumerable<T> GetIListBySql<T>(string sql)
{
using (IDbConnection conn = DBManage.OpenConnection())
{
IEnumerable<T> IElist = (IEnumerable<T>)conn.Query(sql).ToList();
return IElist;
}
}
public static IEnumerable<dynamic> RunSql(string sql, string orderStr, int pageSize, int pageIndex, ref int totalRecord, string connStr = "")
{
using (IDbConnection connection = OpenConnection(connStr))
{
if (totalRecord >= 0)
{
string countSql = "select count(*) as num from (" + sql + ") a";
totalRecord = connection.Query<int>(countSql).Single();
}
sql = "select * from (SELECT ROW_NUMBER() OVER (order by " + orderStr + ") AS Row,* from (" + sql + ") as t) infoData";
int FirstNum = (pageIndex - 1) * pageSize + 1;
int EndNum = pageIndex * pageSize;
sql = sql + " where Row between " + FirstNum.ToString() + " and " + EndNum.ToString();
return connection.Query(sql).ToList();
}
}
public static List<dynamic> RunSqlToList(string sql, string orderStr, int pageSize, int pageIndex, ref int totalRecord, string connStr = "")
{
using (IDbConnection connection = OpenConnection(connStr))
{
if (totalRecord >= 0)
{
string countSql = "select count(*) as num from (" + sql + ") a";
totalRecord = connection.Query<int>(countSql).Single();
}
sql = "select * from (SELECT ROW_NUMBER() OVER (order by " + orderStr + ") AS Row,* from (" + sql + ") as t) infoData";
int FirstNum = (pageIndex - 1) * pageSize + 1;
int EndNum = pageIndex * pageSize;
sql = sql + " where Row between " + FirstNum.ToString() + " and " + EndNum.ToString();
return connection.Query(sql).ToList();
}
}
public static IEnumerable<T> RunSql<T>(string sql, string orderStr, int pageSize, int pageIndex, ref int totalRecord, string connStr = "")
{
using (IDbConnection connection = OpenConnection(connStr))
{
if (totalRecord >= 0)
{
string countSql = "select count(*) as num from (" + sql + ") a";
totalRecord = connection.Query<int>(countSql).Single();
}
sql = "select * from (SELECT ROW_NUMBER() OVER (order by " + orderStr + ") AS Row,* from (" + sql + ") as t) infoData";
int FirstNum = (pageIndex - 1) * pageSize + 1;
int EndNum = pageIndex * pageSize;
sql = sql + " where Row between " + FirstNum.ToString() + " and " + EndNum.ToString();
return connection.Query<T>(sql).ToList();
}
}
}
}
DbHelper
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Text.RegularExpressions;
namespace T4_Practice2
{
/// <summary>
/// 数据访问抽象基础类
/// Copyright (C) Maticsoft
/// </summary>
public class DbHelperSQL
{
//数据库连接字符串(web.config来配置)，多数据库可使用DbHelperSQLP来实现.
public static string connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
// public static string connectionString { get; set; }
public DbHelperSQL() { }
#region 公用方法
/// <summary>
/// 判断是否存在某表的某个字段
/// </summary>
/// <param name="tableName">表名称</param>
/// <param name="columnName">列名称</param>
/// <returns>是否存在</returns>
public static bool ColumnExists(string tableName, string columnName)
{
string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
object res = GetSingle(sql);
if (res == null)
{
return false;
}
return Convert.ToInt32(res) > 0;
}
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
public static bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 表是否存在
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public static bool TabExists(string TableName)
{
string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
object obj = GetSingle(strsql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
public static bool Exists(string strSql, params SqlParameter[] cmdParms)
{
object obj = GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region 执行简单SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
public static int ExecuteSqlByTime(string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static int ExecuteSqlTran(List<String> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch (Exception)
{
tx.Rollback();
return 0;
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static object ExecuteSqlGet(string SQLString, string content)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
public static object GetSingle(string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connection);
try
{
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
string sss = " select * from [Sys_Parameter] where p_id = 2";
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
SqlConnection connection = new SqlConnection(connectionString);
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
public static DataSet Query(string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.SelectCommand.CommandTimeout = Times;
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
#endregion
#region 执行带参数的SQL语句
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}
/// <summary>
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
// finally
// {
// cmd.Dispose();
// connection.Close();
// }
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
#endregion
#region 存储过程操作
/// <summary>
/// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.SelectCommand.CommandTimeout = Times;
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
return command;
}
/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
int result;
connection.Open();
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
//Connection.Close();
return result;
}
}
/// <summary>
/// 创建 SqlCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand 对象实例</returns>
private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
#endregion
/// <summary>
/// 执行带Go的批量语句
/// </summary>
/// <param name="sql"></param>
public static void ExecuteSqlWithGo(String sql)
{
int effectedRows = 0;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
try
{
//注： 此处以 换行_后面带0到多个空格_再后面是go 来分割字符串
String[] sqlArr = Regex.Split(sql.Trim(), "\r\n\\s*go", RegexOptions.IgnoreCase);
foreach (string strsql in sqlArr)
{
if (strsql.Trim().Length > 1 && strsql.Trim() != "\r\n")
{
cmd.CommandText = strsql;
effectedRows = cmd.ExecuteNonQuery();
}
}
}
catch (System.Data.SqlClient.SqlException E)
{
throw new Exception(E.Message);
}
finally
{
conn.Close();
}
}
}
}
}
4.添加引用（R）---(改报错)
引入文件已上传附件:链接: https://pan.baidu.com/s/1ubm30TbmG9b85DnfdQM41Q 提取码: zeeb
5.操作指引
public class homeController : Controller
{
// GET: home
public ActionResult Index()
{
var list=JJQPractice.GetList();
return View();
查询-- - GetList(new { id = 3 }).ToList()
var list1 = JJQPractice.GetList(new { id = 3 }).ToList();//条件查询
var list3 = JJQPractice.GetList().ToList();//全查
////////////////////////////////////////////////////
引入DbHelper(DbHelperSQL)进行直接sql语句查询,,,可以连表查询
string sql1 = "SELECT [id],[name],[type],[date] from [UnifiedAuthoritySystem].[dbo].[JJQPractice]";
var getSet = DbHelperSQL.Query(sql1);//批量查询;;返回DataSet
var getTable = DbHelperSQL.Query(sql1).Tables[0];//批量查询;;返回DataTable
var u = DBManage.GetListBySql<JJQPractice>(sql1);
string sql2 = "insert into [UnifiedAuthoritySystem].[dbo].[JJQPractice](id,name,type) values('9','插入','插入类型')";
string sql3 = "insert into [UnifiedAuthoritySystem].[dbo].[JJQPractice](id,name,type) values('10','插入','插入类型')";
List<string> l = new List<string> { sql2, sql3 };
//var n=DbHelperSQL.ExecuteSql(sql2);//执行sql;返回受影响行数
var m = DbHelperSQL.ExecuteSqlTran(l);//执行slq事务;;返回受影响行数
string sql4 = "SELECT [id],[name],[type],[date] from [UnifiedAuthoritySystem].[dbo].[JJQPractice]where id=20";
var v = DbHelperSQL.Exists(sql4);//查询是否存在符合条件的数据
var dd = DbHelperSQL.GetMaxID("id", "JJQPractice");//返回指定某一列中的最大值+1//GetMaxID(string FieldName, string TableName);不用插入sql语句,,,,,,,,,,,,
////////////////////////////////////////////////////
}
}