using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Collections;
using System.Windows.Forms;
using System.Data.Common;
using System.IO;
namespace httphelper
{
public class SQL_ServerCe
{
public void CreateDataBase(string dabaBaseName)//创建数据库 如C:/test.sdf
{
if (dabaBaseName==string.Empty)
{
MessageBox.Show("创建数据库的格式错误");
return;
}
if (!File.Exists(dabaBaseName))
{
SqlCeEngine engine = new SqlCeEngine(string.Format("data source={0}", dabaBaseName));
engine.CreateDatabase();
if (!File.Exists(dabaBaseName))
{
MessageBox.Show("数据库创建失败");
}
}
else
{
//MessageBox.Show("数据库已经存在");
}
}
public SqlCeConnection ceConnectionServer(SqlCeConnection ceConnnect,string dataSdfPath)
{
string connectString = "data source=";
connectString += dataSdfPath;
if (ceConnnect == null)
{
ceConnnect = new SqlCeConnection(connectString);
}
else
{
ceConnnect.Close();
}
try
{
if (ceConnnect.State == ConnectionState.Broken || ceConnnect.State == ConnectionState.Closed)
{
ceConnnect.Open();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
return ceConnnect;
}
public SqlCeCommand ExceuteCommand(SqlCeConnection ceConnect,string cmdStr)
{
try
{
SqlCeCommand cmd = ceConnect.CreateCommand();
cmd.CommandText = cmdStr;
int newRow = cmd.ExecuteNonQuery();
return cmd;
}
catch (System.Exception ex)
{
//MessageBox.Show(ex.Message);
return null;
}
///return null;
}
public void sql_InsertValue(SqlCeConnection ceConnect,string tableName,object[] field,ArrayList dataList)
{
string fieldStr = null;
for (int i = 0; i < field.Length; i++)
{
fieldStr += field[i] + ",";
}
string dataStr = null;
for (int n = 0; n < dataList.Count; n++)
{
dataStr += dataList[n] + ",";
}
fieldStr = fieldStr.Remove(fieldStr.Length - 1, 1);
dataStr = dataStr.Remove(dataStr.Length - 1, 1);
string cmdStr = string.Format("insert into {0}({1})values({2})", tableName, fieldStr, dataStr);//字符串必须加''
ExceuteCommand(ceConnect, cmdStr);
}
public SqlCeCommand sql_InsertParameter_Init(SqlCeConnection ceConnect, string tableName, object[] field, out int fieldCount, SqlDbType[] sqlType=null)
{
string fieldStr = null;
string questionStr = null;
for (int i = 0; i < field.Length; i++)
{
fieldStr += field[i] + ",";
questionStr += "?,";
}
fieldStr = fieldStr.Remove(fieldStr.Length - 1, 1);//移除末尾的字符串
questionStr = questionStr.Remove(questionStr.Length - 1, 1);
SqlCeCommand cmd=ceConnect.CreateCommand();
//string cmdStr = string.Format("insert into {0}({1})values(?,?,?)", tableName, fieldStr);//字符串必须加''
string cmdStr = string.Format("insert into {0}({1})values({2})", tableName, fieldStr,questionStr);//字符串必须加''
cmd.CommandText = cmdStr;
fieldCount = field.Length; //每个列字段对应一个value
for (int i = 0; i < field.Length; i++)
{
if (sqlType==null)
{
cmd.Parameters.Add(new SqlCeParameter("t" + i.ToString(), SqlDbType.NText));
}
else //有多少个字段就有多少个SqlDbType
{
switch (sqlType[i])
{
case SqlDbType.NChar:
{
cmd.Parameters.Add(new SqlCeParameter("t" + i.ToString(), SqlDbType.NChar));
break;
}
case SqlDbType.NVarChar:
{
cmd.Parameters.Add(new SqlCeParameter("t" + i.ToString(), SqlDbType.NVarChar));
break;
}
case SqlDbType.Image:
{
cmd.Parameters.Add(new SqlCeParameter("t" + i.ToString(), SqlDbType.Image));
break;
}
case SqlDbType.NText:
{
cmd.Parameters.Add(new SqlCeParameter("t" + i.ToString(), SqlDbType.NText));
break;
}
case SqlDbType.Int:
{
cmd.Parameters.Add(new SqlCeParameter("t" + i.ToString(), SqlDbType.Int));
break;
}
case SqlDbType.Money:
{
cmd.Parameters.Add(new SqlCeParameter("t" + i.ToString(), SqlDbType.Money));
break;
}
case SqlDbType.DateTime:
{
cmd.Parameters.Add(new SqlCeParameter("t" + i.ToString(), SqlDbType.DateTime));
break;
}
case SqlDbType.Float:
{
cmd.Parameters.Add(new SqlCeParameter("t" + i.ToString(), SqlDbType.Float));
break;
}
default: { MessageBox.Show("没有找到该数据类型"); break; }
}
}
}
if (cmd.Parameters.Count<=0)
{
return null;
}
//cmd.Parameters.Add(new SqlCeParameter("t1", SqlDbType.Int));
//cmd.Parameters.Add(new SqlCeParameter("t2", SqlDbType.NChar));
//cmd.Parameters.Add(new SqlCeParameter("t3", SqlDbType.NText));
//cmd.Parameters["t1"].Size = 50;
//cmd.Parameters["t2"].Size = 50;
//cmd.Parameters["t3"].Size = 50;
cmd.Prepare();//编译参数,绑定占位符
//cmd.Parameters["t1"].Value = 3;
//cmd.Parameters["t2"].Value = "c"; //字符串无需加''
//cmd.Parameters["t3"].Value = "你好";
//cmd.ExecuteNonQuery();
return cmd;
}
public void sql_InsertParameter_Value(SqlCeCommand cmd, object[] setData, int fieldCount)
{
if (setData.Length<=0)
{
return;
}
try
{
for (int i = 0; i < fieldCount; i++)
{
cmd.Parameters["t" + i.ToString()].Value = setData[i];
}
//cmd.Parameters["t1"].Value = 333;
//cmd.Parameters["t2"].Value = "c"; //字符串无需加''
//cmd.Parameters["t3"].Value = "快件费环境工程都是v";
cmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message+" 或者数据类型不正确");
}
}
public ArrayList sql_query(SqlCeConnection ceConnect, string cmdStr,SqlDbType[] sqlType=null)
{
ArrayList arrList = new ArrayList();
SqlCeCommand cmd=ExceuteCommand(ceConnect, cmdStr);
SqlCeDataReader reader = cmd.ExecuteReader();
int fieldLength = reader.FieldCount;
while (reader.Read())
{
object[] obj=new object[fieldLength];
for (int i = 0; i < fieldLength;i++ ) //根据列的数量获取
{
if (sqlType==null)
{
if (i == 0)//第一个指定是数字ID
{
obj[i] = reader.GetInt32(0);
}
else
{
obj[i] = reader.GetString(i);
}
}
else
{
switch (sqlType[i])
{
case SqlDbType.NVarChar:
{
obj[i] = reader.GetString(i);
break;
}
case SqlDbType.Int:
{
obj[i] = reader.GetInt32(i);
break;
}
case SqlDbType.DateTime:
{
obj[i] = reader.GetDateTime(i);
break;
}
case SqlDbType.Float:
{
obj[i] = reader.GetFloat(i);
break;
}
default: { MessageBox.Show("没有找到该数据类型"); break; }
}
}
//int number = reader.GetInt32(0);
//string str1 = reader.GetString(1);
//string str2 = reader.GetString(2);
}
arrList.Add(obj);
}
return arrList;
}
public void sql_showResultView(SqlCeCommand cmd,string cmdParam,DataGridView uiView)//将查询到的结果进行视图显示
{
//SqlCeResultSet resSet = new SqlCeResultSet();
try
{
cmd.CommandText = cmdParam;
SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
DataSet dataSet = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(dataSet);
uiView.DataSource = dataSet.Tables[0].DefaultView;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public int sql_getRowCount(SqlCeCommand cmd,string tableName)//返回总行数
{
cmd.CommandText = "select * from "+tableName;
SqlCeDataAdapter adapter = new SqlCeDataAdapter(cmd);
DataSet dataSet = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(dataSet);
int row = dataSet.Tables[0].Rows.Count;
dataSet.Dispose();
adapter.Dispose();
return row;
}
public int sql_getRowCompare(SqlCeConnection connect,string sqlTableName,string fileld,string queryValue)//当前值在对应的字段中有多少个
{
int res=0;
string strSql = string.Format("select count(*) from {0} where {1}='{2}'",sqlTableName,fileld,queryValue);//不支持ntext数据类型查询
ArrayList backCount = sql_query(connect, strSql);
for (int i = 0; i < backCount.Count; i++)
{
object[] num = (object[])backCount[i];
res = (int)num[0];
}
return res;
}
}
}