com_base.asmx.cs
来自「ASP.NET的一些开发实例,有论坛管理系统等」· CS 代码 · 共 478 行
CS
478 行
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.IO;
using System.Xml;
using System.Data.SqlClient;
namespace ERPWebService.sysbase
{
/// <summary>
/// COM_BASE 的摘要说明。
/// </summary>
public class COM_BASE : System.Web.Services.WebService
{
protected WhiteForumServer.BdStudioSoft.Gfunction Gfunction=new WhiteForumServer.BdStudioSoft.Gfunction();
protected SqlConnection SqlConn;
protected SqlDataAdapter SqlAD;
public COM_BASE()
{
//CODEGEN：该调用是 ASP.NET Web 服务设计器所必需的
InitializeComponent();
}
#region Component Designer generated code
//Web 服务设计器所必需的
private IContainer components = null;
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.SqlConn=new SqlConnection((string)Application["db_connstring"]);
}
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if(disposing && components != null)
{
components.Dispose();
}
base.Dispose(disposing);
}
#endregion
// WEB 服务示例
// HelloWorld() 示例服务返回字符串 Hello World
// 若要生成，请取消注释下列行，然后保存并生成项目
// 若要测试此 Web 服务，请按 F5 键
// [WebMethod]
// public string HelloWorld()
// {
// return "Hello World";
// }
/// <summary>
/// 查询符合条件的记录
/// </summary>
[WebMethod]
public DataSet Query(ref SqlCommand mySqlCommand,ref SqlConnection myConn,ref SqlTransaction myTrans,ref string str_Sql)
{
DataSet ds =new DataSet();
try
{
mySqlCommand.CommandText = str_Sql;
//mySqlCommand.ExecuteNonQuery();
SqlDataAdapter sqlAD = new SqlDataAdapter(mySqlCommand);
sqlAD.Fill(ds);
}
catch (SqlException e)
{
myTrans.Rollback();
myConn.Close();
return ds;
}
return ds;
}
[WebMethod]
public DataSet CreateDataSetFromSql(string str_Sql)
{
DataSet ds = new DataSet();
try
{
SqlAD = new SqlDataAdapter(str_Sql,SqlConn);
SqlAD.Fill(ds);
}
catch(SqlException e)
{
string str_Err = e.Message.ToString();
}
return ds;
}
[WebMethod]
public string CreateSql(string[] str_FieldValue,string[] str_FieldName,string str_SearchType,string[] str_FieldType)
{
string str_Sql = string.Empty;
if (str_FieldValue != null)
{
for (int i=0;i<str_FieldValue.Length;i++)
{
switch (str_FieldType[i])
{
case "string":
str_Sql = this.CreateSql(str_FieldValue,str_SearchType,str_FieldName);
break;
case "int":
str_Sql = this.CreateSql(str_FieldValue,str_SearchType,str_FieldName);
break;
}
}
}
return str_Sql;
}
//根据参数值，查询条件，字段名生成SQL语句。刘帆
private string CreateSql(string[] str_FieldValue,string str_SearchType,string[] str_FieldName)
{
string str_SqlFilter = string.Empty;
switch (str_SearchType)
{
case "=":
if (str_FieldValue.Length> 0)
{
if(str_FieldValue.Length == 1)
{
str_SqlFilter = string.Format(" {0} = '{1}' ",str_FieldName[0],str_FieldValue[0]);
}
else
{
str_SqlFilter = string.Format(" {0} = '{1}' ",str_FieldName[0],str_FieldValue[0]);
for(int i =1;i<str_FieldValue.Length;i++)
{
str_SqlFilter = string.Format(" {0} and {1} = '{2}' ",str_SqlFilter,str_FieldName[i],str_FieldValue[i]);
}
}
}
break;
case "like":
if (str_FieldValue.Length> 0)
{
if(str_FieldValue.Length == 1)
{
str_SqlFilter = string.Format(" {0} like '%{1}%' ",str_FieldName[0],str_FieldValue[0]);
}
else
{
str_SqlFilter = string.Format(" {0} like '%{1}%' ",str_FieldName[0],str_FieldValue[0]);
for(int i =1;i<str_FieldValue.Length;i++)
{
str_SqlFilter = string.Format(" {0} and {1} like '%{2}%' ",str_SqlFilter,str_FieldName[i],str_FieldValue[i]);
}
}
}
break;
case "in":
if (str_FieldValue.Length> 0)
{
if(str_FieldValue.Length == 1)
{
str_SqlFilter = string.Format(" {0} in ({1}) ",str_FieldName[0],str_FieldValue[0]);
}
else
{
str_SqlFilter = string.Format(" {0} in ({1}) ",str_FieldName[0],str_FieldValue[0]);
for(int i =1;i<str_FieldValue.Length;i++)
{
str_SqlFilter = string.Format(" {0} and {1} in ({2}) ",str_SqlFilter,str_FieldName[i],str_FieldValue[i]);
}
}
}
break;
}
return str_SqlFilter;
}
//根据参数值，查询条件，字段名生成SQL语句。阎水龙
public string CreateSqlString(string[] str_FieldValue,string[] str_FieldName,string[] str_SearchType,string[] str_FieldType)
{
string str_SqlFilter = " ";
for(int i =0;i<str_FieldName.Length;i++)
{
if(str_FieldValue[i].ToString().Trim() !=string.Empty)
{
switch (str_SearchType[i])
{
case "=":
this.Gfunction.StringAdd(ref str_SqlFilter," ",string.Format(" {0} = '{1}' ",str_FieldName[i],str_FieldValue[i])," and ");
break;
case "like":
this.Gfunction.StringAdd(ref str_SqlFilter," ",string.Format(" {0} like '%{1}%' ",str_FieldName[i],str_FieldValue[i])," and ");
break;
case "in":
this.Gfunction.StringAdd(ref str_SqlFilter," ",string.Format(" {0} in ({1}) ",str_FieldName[i],str_FieldValue[i])," and ");
break;
case "not like":
this.Gfunction.StringAdd(ref str_SqlFilter," ",string.Format(" {0} not like '%{1}%' ",str_FieldName[i],str_FieldValue[i])," and ");
break;
}
}
}
return str_SqlFilter;
}
//该函数负责插入数据
[WebMethod]
public int InsertTableBase(ref SqlCommand mySqlCommand,ref SqlConnection myConn,ref SqlTransaction myTrans,string myTableName,DataTable myDataTable,ref string SqlString)
{
DataColumnCollection myCols=myDataTable.Columns;
string SqlInsert = string.Format(" insert into {0} (",myTableName);
int ColumnCount=myDataTable.Columns.Count;
foreach(DataColumn col in myCols)
{
SqlInsert = string.Format(" {0} {1} ,",SqlInsert,col.ColumnName);
}
SqlInsert=SqlInsert.Remove( SqlInsert.Length -1,1);//删除最后一个逗号.
SqlInsert = string.Format(" {0} ) values ( ",SqlInsert);
//执行插入语句
foreach(DataRow row in myDataTable.Rows)
{
SqlString=SqlInsert;
for (int i=0;i<ColumnCount;i++)
{
if (row[i].ToString()=="")
{
SqlString=SqlString+"null,";
}
else
{
SqlString=SqlString+"'"+row[i].ToString()+"',";
}
}
SqlString=SqlString.Remove(SqlString.Length -1,1);//删除最后一个逗号.
SqlString=SqlString+")";
mySqlCommand.CommandText=SqlString;
try
{
mySqlCommand.ExecuteNonQuery();
}
catch(SqlException e)
{
myTrans.Rollback();//取消事务
myConn.Close();//终止连接
return 1;
}
}
return 0;
}
[WebMethod]
public int UpdateTableBase(ref SqlCommand mySqlCommand,ref SqlConnection myConn,ref SqlTransaction myTrans,string myTableName,DataTable myDataTable,ref string SqlString,ref string s_pk)
{
DataColumnCollection myCols=myDataTable.Columns;
//string sPrimaryKey=myDataTable.PrimaryKey[0].ToString();
string sPrimaryKey = s_pk;
string SqlInsert="update "+myTableName+" set ";
//执行修改语句
foreach(DataRow row in myDataTable.Rows)
{
SqlString=SqlInsert;
string SqlWhere = string.Format("{0} = '{1}'",sPrimaryKey,row[sPrimaryKey].ToString());
myCols.Remove(s_pk);
foreach(DataColumn col in myCols)
{
if (row[col.ColumnName].ToString()=="")
{
SqlString=SqlString+col.ColumnName+"="+"null,";
}
else
{
SqlString=SqlString+col.ColumnName+"="+"'"+row[col.ColumnName].ToString()+"',";
}
}
SqlString=SqlString.Remove(SqlString.Length -1,1);//删除最后一个逗号.
//SqlString=SqlString+" where "+sPrimaryKey+"=";
//SqlString=SqlString+"'"+row[sPrimaryKey].ToString()+"'";
SqlString = string.Format("{0} where {1} ",SqlString,SqlWhere);
mySqlCommand.CommandText=SqlString;
try
{
mySqlCommand.ExecuteNonQuery();
}
catch(SqlException e)
{
myTrans.Rollback();//取消事务
myConn.Close();//终止连接
return 1;
}
}
return 0;
}
[WebMethod]
public int UpdateTableBaseFromSQL(ref SqlCommand mySqlCommand,ref SqlConnection myConn,ref SqlTransaction myTrans,string SqlString)
{
mySqlCommand.CommandText=SqlString;
try
{
mySqlCommand.ExecuteNonQuery();
}
catch(SqlException e)
{
myTrans.Rollback();//取消事务
myConn.Close();//终止连接
return 1;
}
return 0;
}
//该函数负责删除数据
[WebMethod]
public int DeleteTableBase(ref SqlCommand mySqlCommand,ref SqlConnection myConn,ref SqlTransaction myTrans,string myTableName,ref string SqlString)
{
SqlString = string.Format("delete {0} where {1} ",myTableName,SqlString);
//执行删除语句
mySqlCommand.CommandText=SqlString;
try
{
mySqlCommand.ExecuteNonQuery();
}
catch(SqlException e)
{
myTrans.Rollback();//取消事务
myConn.Close();//终止连接
return 1;
}
return 0;
}
//制作根据数据集按照指定条件，取出指定字段的值的公共函数
[WebMethod]
public string GetFieldValue(string FieldName,string FieldValue,string TableName,string GetFieldName)
{
string strGetFieldValue = string.Empty;
string[] strFieldValue = {FieldValue};
string[] strFieldName = {FieldName};
string SearchType = "=";
string strWhere = this.CreateSql(strFieldValue,SearchType,strFieldName);
string strSql = string.Format(" select {0} from {1} where {2}",GetFieldName,TableName,strWhere);
try
{
DataSet ds = this.CreateDataSetFromSql(strSql);
strGetFieldValue=ds.Tables[0].Rows[0][GetFieldName].ToString();
}
catch
{
strGetFieldValue = string.Empty;
}
return strGetFieldValue;
}
//该函数负责生成表
[WebMethod]
public int CreateTable(ref SqlCommand mySqlCommand,ref SqlConnection myConn,ref SqlTransaction myTrans,ref string myTableName,ref string type,ref string SqlString)
{
string strSql = string.Empty;
if (SqlString.Trim() != string.Empty)
{
if (type == "1")
{
strSql =string.Format(" drop Table [{0}]",myTableName);
}
if (type == "2")
{
strSql = string.Format(" drop view [{0}]",myTableName);
}
this.CreateDataSetFromSql(strSql);
}
//执行删除语句
mySqlCommand.CommandText=SqlString;
try
{
mySqlCommand.ExecuteNonQuery();
}
catch(SqlException e)
{
myTrans.Rollback();//取消事务
myConn.Close();//终止连接
return 1;
}
myTrans.Commit();
myConn.Close();
return 0;
}
/// <summary>
/// 该函数负责生成建表的Sql语句。
/// </summary>
[WebMethod]
public string CreateTableSql(string TableName,DataSet ds)
{
string sqlCreateTable = string.Empty;
string strPk = string.Empty;
string[] str_field = {"t_name","t_dtype","t_size","t_decimal","t_pk"};
if (ds.Tables[0].Rows.Count > 0)
{
for (int i=0;i<ds.Tables[0].Rows.Count;i++)
{
str_field[0] = ds.Tables[0].Rows[i]["t_name"].ToString();
str_field[1] = ds.Tables[0].Rows[i]["t_dtype"].ToString();
str_field[2] = ds.Tables[0].Rows[i]["t_size"].ToString();
str_field[3] = ds.Tables[0].Rows[i]["t_decimal"].ToString();
str_field[4] = ds.Tables[0].Rows[i]["t_pk"].ToString();
switch (str_field[1])
{
case "int":
str_field[2] = string.Empty;
break;
case "decimal":
str_field[2] = string.Format("({0},{1})",str_field[2],str_field[3]);
break;
case "datetime":
str_field[2] = string.Empty;
break;
case "ntext":
str_field[2] = string.Empty;
break;
default:
str_field[2] = string.Format("({0})",str_field[2]);
break;
}//switch
if (str_field[4] == "1")
{
str_field[4] = " NOT NULL ";
strPk = string.Format( "{0} {1} ,",strPk,str_field[0].ToString());
}
else
{
str_field[4] = string.Empty;
}
sqlCreateTable = string.Format(" {0} [{1}] [{2}]{3} {4} ,",sqlCreateTable,str_field[0],str_field[1],str_field[2],str_field[4]);
sqlCreateTable = sqlCreateTable.Remove(sqlCreateTable.Length-1,1);
strPk = strPk.Remove(strPk.Length-1,1);
sqlCreateTable = string.Format(" create Table [{0}] ({1} primary key ({2}) ); ",TableName,sqlCreateTable,strPk);
}//for
}// if
return sqlCreateTable;
}
[WebMethod]
public string GetID(string strSql)
{
string strID = string.Empty;
DataSet ds = this.CreateDataSetFromSql(strSql);
if (ds.Tables[0].Rows.Count > 0)
{
strID = ds.Tables[0].Rows[ds.Tables[0].Rows.Count-1][0].ToString();
}
return strID;
}
}
}
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?