数据库操作

来源：互联网发布：淘宝买家信誉显示7心编辑：程序博客网时间：2024/06/03 16:04

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace barcodewebservice
{
public class DBOperate
{
#region 私有变量
private string _strConn;
#endregion

#region 属性
public string strConn
{
get
{
return _strConn;
}
set
{
_strConn = value;
}

}
#endregion
public string strError;
public DBOperate(string strconn)
{
_strConn = strconn;
}
//查询
public DataTable Select(string strSQL)
{
using (SqlConnection connection = new SqlConnection(_strConn))
{
DataTable dataTable = new DataTable();
try
{
SqlDataAdapter adapter = new SqlDataAdapter(strSQL, _strConn);
adapter.SelectCommand.CommandTimeout = 300;//2015-05-06 查询超时 ,在链接字符串设置似乎无效
adapter.Fill(dataTable);
return dataTable;

}
catch (SqlException ex)
{
dataTable = null;

strError = ex.Message.ToString();
return dataTable;
//throw ex;
}
}

}
//更新,删除，插入,并返回受影响的行数
public int ExecuteSql(string strSQL)
{
int rows;
using (SqlConnection connection = new SqlConnection(_strConn))
{
try
{
SqlCommand command = new SqlCommand(strSQL, connection);
command.Connection.Open();
rows= command.ExecuteNonQuery();

command.Connection.Close();
return rows;
}
catch (SqlException ex)
{
strError = ex.Message.ToString();
return -999;
// throw ex;
}
}
}
public int uuidcheck(string strUUID)
{
using (SqlConnection connection = new SqlConnection(_strConn))
{
DataTable dataTable = new DataTable();
try
{
SqlDataAdapter adapter = new SqlDataAdapter("exec ESusercheck '"+strUUID +"'", _strConn);
adapter.Fill(dataTable);
return (int)dataTable.Rows[0][0];

}
catch (SqlException ex)
{

return -1;
//throw ex;
}
}
}


}

}

------------------------------------

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.IO;
namespace barcodewebservice
{
/// <summary>
/// Service1 的摘要说明
/// </summary>
[WebService(Namespace = "http://easyscan.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
// 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。
// [System.Web.Script.Services.ScriptService]
public class Service1 : System.Web.Services.WebService
{
public static string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();
public static string strK3DebugConn = ConfigurationManager.AppSettings["K3DebugConnectionString"].ToString();
public static string strK3Conn = ConfigurationManager.AppSettings["K3ConnectionString"].ToString();
public DBOperate sqlhelp = new DBOperate(strConn);
//[WebMethod]
//public string HelloWorld(int a)
//{
// DataTable dt;

// dt = sqlhelp.Select("select * from tb");

// return dt.Rows[0][0].ToString() ;
//}
[WebMethod]
public List<string> getdata(string strsql, string strYear)
{
//获取返回的结果
int i, j, k;
DataTable dt;
List<string> ll = new List<string>();
//先安全性验证
k = sqlhelp.uuidcheck(strYear);
if (1 != k)
{
//只要k不等于1，就认为验证无法通过
ll.Add("-999");
ll.Add("该设备未注册!");
return ll;
}
dt = sqlhelp.Select(strsql);
if (dt == null)//表示select执行出错
{
ll.Add("-999");
ll.Add(sqlhelp.strError);
}

else
{//eg:<x>3</x><x>4</x><x>f1</x><x>f2</x><x>a</x><x>b</x>
ll.Add(dt.Rows.Count.ToString());//行数
ll.Add(dt.Columns.Count.ToString());//列数
//列名
for (i = 0; i < dt.Columns.Count; i++)
{
ll.Add(dt.Columns[i].ColumnName);
}
//数据
for (i = 0; i < dt.Rows.Count; i++)
for (j = 0; j < dt.Columns.Count; j++)
{
ll.Add(dt.Rows[i][j].ToString());
}
}
//ll.Add(dt.Rows.Count.ToString());
//ll.Add(dt.Columns.Count.ToString());
//for (i = 0; i < dt.Rows.Count; i++)
//{
// ll.Add(dt.Rows[i][2].ToString());
// ll.Add(dt.Columns[i].ColumnName);
//}
dt = null;
return ll;
}
[WebMethod]
public List<string> getrows(string strsql, string strYear)
{
//获取受影响的行数,如果执行错误，就返回错误信息
List<string> ll = new List<string>();
//安全性验证
int k = sqlhelp.uuidcheck(strYear);
if (strsql.IndexOf("from@#$#*") > 0)//设备初次注册
{
k = 1;
strsql = strsql.Replace("@#$#*", "");
}
if (1 != k)
{
//只要k不等于1，就认为验证无法通过
ll.Add("-999");
ll.Add("E该设备未注册!");
return ll;
}
int rows;
rows = sqlhelp.ExecuteSql(strsql);
if (rows != -999)
{
ll.Add(rows.ToString());
}
else if (rows == -999)
{
ll.Add("E" + sqlhelp.strError);
}
return ll;
}
[WebMethod]
public List<string> GD_bar_SaveBill(string strhead,string strentry, string strYear)
{
List<string> ll = new List<string>();
try
{
//获取受影响的行数,如果执行错误，就返回错误信息

//安全性验证
int k = sqlhelp.uuidcheck(strYear);
if (1 != k)
{
//只要k不等于1，就认为验证无法通过
ll.Add("-999");
ll.Add("E该设备未注册!");
return ll;
}
int rows;
//write debug log
// sqlhelp.ExecuteSql("insert into t_easy_billdataLog(fhead,fentry,fuuid) select '" + strhead + "','" + strentry + "','"+strYear +"'");//log

DataTable dt;
//20150710 少数时候保存会出现连续调用2次webservice的现象，原因暂未查出
//暂行解决办法:如果60秒之内，出现过表头相等，表体前2000内容相等,且uuid一样，可以近似认为是重复调用
//2015-08-05 打印不做重复提交控制 qk
dt = sqlhelp.Select("select 1 from t_easy_billdataLog where fentry<>'x' and fhead='" + strhead + "' and fkey='" + strentry.Substring(0, strentry.Length>=2000?2000:strentry.Length) + "' and fuuid='" + strYear + "' and datediff(s,fdate,getdate())<=60 ");
if (dt != null && dt.Rows.Count > 0)//表示select执行出错
{
sqlhelp.ExecuteSql("insert into t_easy_billdataLog(fhead,fentry,fkey,fuuid) select '" + strhead + "','" + "提交冲突" + "','" +"" + "','" + strYear + "'");//log

ll.Add("1");
ll.Add("提交冲突,请到系统查看是否生成单据,请勿再次提交!");
dt = null;
return ll;
}
sqlhelp.ExecuteSql("insert into t_easy_billdataLog(fhead,fentry,fkey,fuuid) select '" + strhead + "','" + strentry + "','" + strentry.Substring(0, strentry.Length >= 2000 ? 2000 : strentry.Length) + "','" + strYear + "'");//log

KDSaveBill.savebill kdsavebill = new KDSaveBill.savebill();
String str = kdsavebill.KDSaveBillInterface(strhead, strentry, strYear, strK3Conn);
// System.Threading.Thread.Sleep(180000);
ll.Add("1");
ll.Add(str);

return ll;
}
catch (Exception e)
{
ll.Add("1");
ll.Add(e.Message.ToString());

return ll;
}
}
private int SecureVerification(String strtoken)
{
//安全验证
//strtoken为口令，由加密的唯一码组成,验证在数据库中是否存在，如果不存在，则认为是未经过注册的手机

return sqlhelp.uuidcheck(strtoken);
}

}
}

------------

</configSections>
<appSettings>
<add key="K3DebugConnectionString" value="ConnectString={Persist Security Info=True;Provider=SQLOLEDB.1;User ID=qiankun;Password=qiankun19860205;Data Source=192.168.0.251;Initial Catalog=uitest5};UserName=qiankun;UserID=16450;DBMS Name=Microsoft SQL Server;DBMS Version=2000/2005;SubID=super;AcctType=gy;Setuptype=Industry;Language=chs;IP=172.16.8.250;K3Version=KUE;MachineName=QIANKUN-246;UUID=65587327-05C0-4499-8423-104346C2E467" />
<add key="K3ConnectionString" value="ConnectString={Persist Security Info=True;Provider=SQLOLEDB.1;User ID=qiankun;Password=xxx;Data Source=xxx;Initial Catalog=xxx};UserName=xxx;UserID=xxx;DBMS Name=Microsoft SQL Server;DBMS Version=2000/2005;SubID=super;AcctType=gy;Setuptype=Industry;Language=chs;IP=xxx;K3Version=KUE;MachineName=xxx-246;UUID=65587327-05C0-4499-8423-104346C2E467" />
</appSettings>
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx;" providerName="System.Data.SqlClient" />
</connectionStrings>

0 0