private void BT_BuildSql_Click(object sender, EventArgs e)
{
RTB_Content.Clear();
DataSet ds = CExcel.Import_Excel(TB_BrowseFileName.Text);
if (ds != null)
{
int final = ds.Tables[0].Rows.Count - 1;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (i == 0)
{
RTB_Content.Text += CSql.GetDataTableNameString(TB_Name.Text) + "\n";
}
else if (i == ds.Tables[0].Rows.Count - 1)
{
RTB_Content.Text += CSql.GetDataTableColumnString(ds.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][3].ToString(), true) + "\n";
}
else
{
RTB_Content.Text += CSql.GetDataTableColumnString(ds.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][3].ToString(), false) + "\n";
}
}
}
}
public RawFileImporter(string ServerName, string DatabaseName, string ImportPath)
{
m_ServerName = ServerName;
m_DatabaseName = DatabaseName;
m_Importpath = ImportPath;
string ConnString = string.Format("Data Source={0}; Initial Catalog={1};Integrated Security=SSPI", m_ServerName, m_DatabaseName);
m_Csql = new CSql(ConnString);
}
protected void Page_Load(object sender, EventArgs e)
{
string sql = "select xh,xm from student ";
DataSet ds = CSql.CreateDataSet(sql);
Response.Clear();
Response.Write(JsonHelper.Convert2Json(ds.Tables[0]));
Response.End();
}
private static void WriteCartList(StringBuilder sb, DataRow row, string cid)
{
sb.Append("{");
foreach (DataColumn column in row.Table.Columns)
{
sb.AppendFormat("\"{0}\":", column.ColumnName);
WriteValue(sb, row[column]);
sb.Append(",");
}
string cartid = string.Join("','", cid.Split(' '));
string sql = "select distinct id, proId, title, img, originPrice, price, count FROM V_Cart where id IN('" + cartid + "')";
DataTable table = CSql.CreateDataSet(sql, "CartData").Tables[0];
WriteSubDataTable(sb, table);
sb.Append("}");
}
private static void StartPools()
{
StartPool(true); //start master pool
StartPool(false); //start slave pool
CSql handler = Master.SeekByQueue();
if (handler != null) //create a test database
{
string sql = @"DROP DATABASE mysample;CREATE DATABASE mysample character set utf8 collate utf8_general_ci;
USE mysample;CREATE TABLE COMPANY(ID BIGINT PRIMARY KEY NOT NULL,Name CHAR(64)NOT NULL);
CREATE TABLE EMPLOYEE(EMPLOYEEID BIGINT PRIMARY KEY AUTO_INCREMENT,CompanyId BIGINT NOT NULL,Name NCHAR(64)
NOT NULL,JoinDate DATETIME(6)DEFAULT NULL,FOREIGN KEY(CompanyId)REFERENCES COMPANY(id));USE sakila";
bool ok = handler.Execute(sql);
sql = "INSERT INTO mysample.COMPANY(ID,Name)VALUES(1,'Google Inc.'),(2,'Microsoft Inc.'),(3,'Amazon Inc.')";
ok = handler.Execute(sql);
}
}
private void BT_SqlInsert_Click(object sender, EventArgs e)
{
RTB_Content.Clear();
DataSet ds = CExcel.Import_Excel(TB_BrowseFileName.Text);
string[] ColumnName = new string[ds.Tables[0].Rows.Count - 1];
string[] ColumnMean = new string[ds.Tables[0].Rows.Count - 1];
string[] ColumnStatus = new string[ds.Tables[0].Rows.Count - 1];
for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{
ColumnName[i] = ds.Tables[0].Rows[i + 1][0].ToString();
ColumnMean[i] = ds.Tables[0].Rows[i + 1][2].ToString();
ColumnStatus[i] = ds.Tables[0].Rows[i + 1][3].ToString();
}
if (ds != null)
{
RTB_Content.Text = CSql.SCADA_Insert(ColumnName, TB_Name.Text, ColumnStatus);
}
}
private static void WriteShop(StringBuilder sb, DataRow row, string sid)
{
sb.Append("{");
foreach (DataColumn column in row.Table.Columns)
{
sb.AppendFormat("\"{0}\":", column.ColumnName);
WriteValue(sb, row[column]);
sb.Append(",");
}
string sql = "select distinct id, proId, title, originPrice, price, count FROM V_Cart where isPay='0' AND shopID='" + sid + "'";
DataTable table = CSql.CreateDataSet(sql, "CartData").Tables[0];
WriteSubDataTable(sb, table);
// Remove the trailing comma.
//if (row.Table.Columns.Count > 0)
//{
// --sb.Length;
//}
sb.Append("}");
}
// {orderList: [
// {shop: ..., CartData: [{},...] }},
// {}
//]}
public static string GetOrderListJSon(string uid, string ispay)
{
StringBuilder sb = new StringBuilder();
string sql = "SELECT id, uid, sid, shopName, shopIcon, payway, isPay, cartIdList, originPrice, discount, deliveryFee, remark FROM V_Order WHERE isPay LIKE'" + ispay + "' and uid='" + uid + "'";
DataTable table = CSql.CreateDataSet(sql, "OrderList").Tables[0];
string name = table.TableName;
sb.Append("{\"" + name + "\":[");
foreach (DataRow row in table.Rows)
{
string cid = row["cartIdList"].ToString();
WriteCartList(sb, row, cid);
sb.Append(",");
}
// Remove the trailing comma.
if (table.Rows.Count > 0)
{
--sb.Length;
}
sb.Append("]}");
return(sb.ToString());
}
public static string GetCartJSon(string uid)
{
StringBuilder sb = new StringBuilder();
string sql = "select distinct shopID, shopName, shopIcon FROM V_Cart where isPay='0' AND uid='" + uid + "'";
DataTable table = CSql.CreateDataSet(sql, "Shops").Tables[0];
string name = table.TableName;
sb.Append("{\"" + name + "\":[");
foreach (DataRow row in table.Rows)
{
string sid = row["shopID"].ToString();
WriteShop(sb, row, sid);
sb.Append(",");
}
// Remove the trailing comma.
if (table.Rows.Count > 0)
{
--sb.Length;
}
sb.Append("]}");
return(sb.ToString());
}
private void BT_BuildCs_Click(object sender, EventArgs e)
{
RTB_Content.Clear();
DataSet ds = CExcel.Import_Excel(TB_BrowseFileName.Text);
if (ds != null)
{
string[] ColumnName = new string[ds.Tables[0].Rows.Count - 1];
string[] ColumnMean = new string[ds.Tables[0].Rows.Count - 1];
string[] ColumnStatus = new string[ds.Tables[0].Rows.Count - 1];
for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{
ColumnName[i] = ds.Tables[0].Rows[i + 1][0].ToString();
ColumnMean[i] = ds.Tables[0].Rows[i + 1][2].ToString();
ColumnStatus[i] = ds.Tables[0].Rows[i + 1][3].ToString();
}
RTB_Content.Text += CSql.GetCsUsingString();
RTB_Content.Text += CSql.GetCsClassHeadString(TB_Name.Text);
RTB_Content.Text += CSql.GetCsClassShuXingString(ColumnName);
RTB_Content.Text += CSql.GetCsClassRegionString(ColumnName, ColumnMean);
RTB_Content.Text += CSql.GetCsClassFunctionString(ColumnName, TB_Name.Text, ColumnStatus);
}
}
private void RunScript(string scriptname)
{
//if nothing to run, return
if (string.IsNullOrEmpty(scriptname))
{
return;
}
Cursor saveCur = this.Cursor;
string FullScriptName;
try
{
this.Cursor = Cursors.WaitCursor;
Application.DoEvents();
MainForm.LogMessage(string.Format("Executing {0}...", scriptname));
// Server srv = new Server(Globals.Server);
//Database db = srv.Databases[Globals.Database];
MainForm.LogMessage("db name = " + Globals.credentialMgr.Database);
if (-1 == scriptname.IndexOf('\\'))
{
FullScriptName = Application.StartupPath + "\\" + scriptname;
if (!File.Exists(FullScriptName))
{
FullScriptName = Application.StartupPath + @"\Reports\" + scriptname;
if (!File.Exists(FullScriptName))
{
FullScriptName = Globals.AppDataPath + "\\" + scriptname;
if (!File.Exists(FullScriptName))
{
FullScriptName = Globals.AppDataPath + "\\reports\\" + scriptname;
}
}
}
}
else
{
FullScriptName = scriptname;
}
if (!File.Exists(FullScriptName))
{
MainForm.LogMessage("Script " + FullScriptName + "doesn't exist", MessageOptions.All);
return;
}
//db.ExecuteNonQuery(File.ReadAllText(FullScriptName), Microsoft.SqlServer.Management.Common.ExecutionTypes.ContinueOnError);
CSql mysql = new CSql(Globals.credentialMgr.ConnectionString);
//MainForm.LogMessage("Connection string inside execute script " + Globals.credentialMgr.ConnectionString);
mysql.ExecuteSqlScript(File.ReadAllText(FullScriptName));
MainForm.LogMessage("full script " + FullScriptName);
MainForm.LogMessage(string.Format("Execution of {0} complete.", FullScriptName));
}
finally
{
this.Cursor = saveCur;
}
}