private static DataSet GetJoinedDate(int StaffID, ref DateTime dt)
{
DataSet ds = null;
try
{
string str = "CREATE PROCEDURE [dbo].[sp_GetJoinedDate](@StaffID int,@JoinedDate datetime output)";
// Parse the stored procedure signature
SPSignature signature = new SPSignature(str);
SPCaller caller = new SPCaller(signature);
caller.ConnectionStr = ConnectionStr;
Output outputDT = new Output(dt);
ds = caller.CallDataSetProc("[dbo].[sp_GetJoinedDate]", StaffID, outputDT);
dt = outputDT.GetDateTime();
}
catch (System.Exception ex)
{
Debug.Print(ex.Message);
}
return(ds);
}
private static void InsertEmployee(
ref int ID,
string Name,
string Title,
string Address,
decimal Salary,
DateTime JoinedDate,
byte?Children)
{
try
{
string str = "CREATE PROCEDURE [dbo].[sp_InsertEmp](";
str += "@ID int OUTPUT," + "@Name nvarchar(30),";
str += "@Title varchar(20)," + "@Address varchar(30),";
str += "@Salary money," + "@JoinedDate datetime,";
str += "@Children tinyint)";
// Parse the stored procedure signature
SPSignature signature = new SPSignature(str);
SPCaller caller = new SPCaller(signature);
caller.ConnectionStr = ConnectionStr;
Output outputID = new Output(ID);
caller.CallVoidProc("[dbo].[sp_InsertEmp]",
outputID, Name, Title, Address, Salary, JoinedDate, Children);
ID = outputID.GetInt();
}
catch (System.Exception ex)
{
Debug.Print(ex.Message);
}
}
private void btnParse_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(txtFunctionName.Text))
{
MessageBox.Show("Please fill in the function name");
return;
}
if (string.IsNullOrEmpty(txtSQL.Text))
{
MessageBox.Show("Please fill in the SQL statement");
return;
}
_Signature = new SPSignature();
_Signature.Name = txtFunctionName.Text;
List <string> listParamStr = _Signature.GetAllParam(txtSQL.Text);
if (listParamStr.Count == 0)
{
MessageBox.Show("No @ parameters found");
return;
}
_ListParam = new List <Parameter>();
foreach (string str in listParamStr)
{
_ListParam.Add(new Parameter {
Name = str, SQLType = "NVarChar", TextLength = "50"
});
}
SQLType type = _Signature.GetType(txtSQL.Text);
if (type == SQLType.INSERT)
{
chkReturnPrimaryKey.IsEnabled = true;
}
else
{
chkReturnPrimaryKey.IsEnabled = false;
}
if (type == SQLType.SELECT)
{
chkUseSQLReader.IsEnabled = true;
}
else
{
chkUseSQLReader.IsEnabled = false;
}
listBoxSQPParameterType.ItemsSource = _ListParam;
btnGenerateCode.IsEnabled = true;
}
private static string GetJoinedDateCode()
{
string code = null;
try
{
string str = "CREATE PROCEDURE [dbo].[sp_GetJoinedDate](@StaffID int,@JoinedDate datetime output)";
// Parse the stored procedure signature
SPSignature signature = new SPSignature(str);
code = SPCallerGen.GenCode(signature, "GetJoinedDate", SPCallerGen.ReturnType.Tables, null, null, false);
}
catch (System.Exception ex)
{
Debug.Print(ex.Message);
}
return(code);
}
private static string GetAllEmployeeCode()
{
string code = null;
try
{
string str = "CREATE PROCEDURE [dbo].[sp_GetAllEmployee]";
// Parse the stored procedure signature
SPSignature signature = new SPSignature(str);
code = SPCallerGen.GenCode(signature, "GetAllEmployee", SPCallerGen.ReturnType.Tables, null, null, false);
}
catch (System.Exception ex)
{
Debug.Print(ex.Message);
}
return(code);
}
public static void InsertEmployee(
ref int ID,
string Name,
string Title,
string Address,
decimal Salary,
DateTime JoinedDate,
byte?Children)
{
try
{
string str = "CREATE PROCEDURE [dbo].[sp_InsertEmp](";
str += "@ID int OUTPUT," + "@Name nvarchar(30),";
str += "@Title varchar(20)," + "@Address varchar(30),";
str += "@Salary money," + "@JoinedDate datetime,";
str += "@Children tinyint)";
// Parse the stored procedure signature
SPSignature signature = new SPSignature(str);
string location = System.Reflection.Assembly.GetExecutingAssembly().Location;
string dir = System.IO.Path.GetDirectoryName(location);
string xmlPath = System.IO.Path.Combine(dir, "signature.xml");
signature.Save(xmlPath);
SPSignature signature2 = new SPSignature();
signature2.Load(xmlPath);
SPCaller caller = new SPCaller(signature2);
caller.ConnectionStr = ConnectionStr;
Output outputID = new Output(ID);
caller.CallVoidProc("[dbo].[sp_InsertEmp]",
outputID, Name, Title, Address, Salary, JoinedDate, Children);
ID = outputID.GetInt();
}
catch (System.Exception ex)
{
Debug.Print(ex.Message);
}
}
private static int GetNum()
{
int n = -1;
try
{
string str = "CREATE PROCEDURE [dbo].[sp_GetNum] ";
// Parse the stored procedure signature
SPSignature signature = new SPSignature(str);
SPCaller caller = new SPCaller(signature);
caller.ConnectionStr = ConnectionStr;
n = caller.CallIntProc("[dbo].[sp_GetNum]");
}
catch (System.Exception ex)
{
Debug.Print(ex.Message);
}
return(n);
}
private static DataSet GetAllEmployee()
{
DataSet ds = null;
try
{
string str = "CREATE PROCEDURE [dbo].[sp_GetAllEmployee]";
// Parse the stored procedure signature
SPSignature signature = new SPSignature(str);
SPCaller caller = new SPCaller(signature);
caller.ConnectionStr = ConnectionStr;
ds = caller.CallDataSetProc("[dbo].[sp_GetAllEmployee]");
}
catch (System.Exception ex)
{
Debug.Print(ex.Message);
}
return(ds);
}
private static string GetChildrenCode(int StaffID)
{
string code = null;
try
{
string str = "CREATE PROCEDURE [dbo].[sp_GetChildren](@StaffID int )";
// Parse the stored procedure signature
SPSignature signature = new SPSignature(str);
SPCaller caller = new SPCaller(signature);
caller.ConnectionStr = ConnectionStr;
code = caller.GenDataSetProcCode("[dbo].[sp_GetChildren]", StaffID);
}
catch (System.Exception ex)
{
Debug.Print(ex.Message);
}
return(code);
}
private static string GetNumCode()
{
string code = null;
try
{
string str = "CREATE PROCEDURE [dbo].[sp_GetNum] ";
// Parse the stored procedure signature
SPSignature signature = new SPSignature(str);
SPCaller caller = new SPCaller(signature);
caller.ConnectionStr = ConnectionStr;
code = caller.GenIntProcCode("[dbo].[sp_GetNum]");
}
catch (System.Exception ex)
{
Debug.Print(ex.Message);
}
return(code);
}
private static string InsertEmployeeCode()
{
string code = null;
try
{
string str = "CREATE PROCEDURE [dbo].[sp_InsertEmp](";
str += "@ID int OUTPUT," + "@Name nvarchar(30),";
str += "@Title varchar(20)," + "@Address varchar(30),";
str += "@Salary money," + "@JoinedDate datetime,";
str += "@Children tinyint)";
// Parse the stored procedure signature
SPSignature signature = new SPSignature(str);
code = SPCallerGen.GenCode(signature, "InsertEmployee", SPCallerGen.ReturnType.None, null, null, false);
}
catch (System.Exception ex)
{
Debug.Print(ex.Message);
}
return(code);
}
private void btnGenCode_Click(object sender, RoutedEventArgs e)
{
string msg = null;
if (string.IsNullOrEmpty(txtSignature.Text))
{
msg = "Signature is not specified.";
}
if (string.IsNullOrEmpty(txtMethodName.Text))
{
msg = "Method Name is not specified.";
}
if (string.IsNullOrEmpty(msg) == false)
{
MessageBox.Show(msg);
return;
}
string code = string.Empty;
if (chkMySql.IsChecked == false)
{
SPCallerGen.ReturnType retType = SPCallerGen.ReturnType.Tables;
if (radioTables.IsChecked == true)
{
retType = SPCallerGen.ReturnType.Tables;
}
else if (radioInteger.IsChecked == true)
{
retType = SPCallerGen.ReturnType.Integer;
}
else if (radioNone.IsChecked == true)
{
retType = SPCallerGen.ReturnType.None;
}
else
{
MessageBox.Show("No return type is selected.");
return;
}
SPSignature signature = new SPSignature();
try
{
signature.Parse(txtSignature.Text + " ", chkNoNullableTypes.IsChecked == true);
if (signature.HasTableParam)
{
GetTableScriptWin form = new GetTableScriptWin();
form.SProcSignature = signature;
form.ShowDialog();
if (form.TableTypeSignatureList.Count != signature.TableParamNum)
{
MessageBox.Show("Error with getting table script", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
foreach (TableTypeSignature ttSign in form.TableTypeSignatureList)
{
code += TableTypeGen.GenCode(ttSign);
}
}
code += SPCallerGen.GenCode(signature, txtMethodName.Text, retType, null, null, false);
}
catch (System.Exception ex)
{
MessageBox.Show("Error:" + ex.Message);
return;
}
}
else
{
SPMySQLCallerGen.ReturnType retType = SPMySQLCallerGen.ReturnType.Tables;
if (radioTables.IsChecked == true)
{
retType = SPMySQLCallerGen.ReturnType.Tables;
}
else if (radioInteger.IsChecked == true)
{
retType = SPMySQLCallerGen.ReturnType.Integer;
}
else if (radioNone.IsChecked == true)
{
retType = SPMySQLCallerGen.ReturnType.None;
}
else
{
MessageBox.Show("No return type is selected.");
return;
}
SPMySQLSignature signature = new SPMySQLSignature();
try
{
signature.Parse(txtSignature.Text, chkNoNullableTypes.IsChecked == true);
code += SPMySQLCallerGen.GenCode(signature, txtMethodName.Text, retType, null, null, false);
}
catch (System.Exception ex)
{
MessageBox.Show("Error:" + ex.Message);
return;
}
}
if (string.IsNullOrEmpty(code) == false)
{
Clipboard.SetData(DataFormats.Text, code);
MessageBox.Show("The auto-generated code has been copied to clipboard!", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("No code is generated", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void CommandExecuted(object sender, RoutedEventArgs e)
{
if ((e as ExecutedRoutedEventArgs).Command == ApplicationCommands.Paste)
{
// verify that the textbox handled the paste command
if (e.Handled)
{
string signLower = txtSignature.Text.ToLower();
int pos = signLower.IndexOf("proc");
if (pos == -1)
{
return;
}
bool PosInSpaceBefProcName = false;
bool PosProcName = false;
string spname = string.Empty;
for (int i = pos; i < txtSignature.Text.Length; ++i)
{
char c = txtSignature.Text[i];
if (PosInSpaceBefProcName == false)
{
if (SPSignature.IsWhitespace(c) == false)
{
continue;
}
else
{
PosInSpaceBefProcName = true;
}
}
else if (PosInSpaceBefProcName && PosProcName == false)
{
if (SPSignature.IsWhitespace(c))
{
continue;
}
else
{
spname += c;
PosProcName = true;
}
}
else if (PosProcName)
{
if (SPSignature.IsWhitespace(c) == false && c != '(')
{
spname += c;
}
else
{
break;
}
}
}
txtMethodName.Text = spname.Trim();
}
}
}
protected override void WndProc(ref Message m)
{
if (m.Msg == WM_PASTE)
{
if (Clipboard.ContainsText() == true)
{
string sign = Clipboard.GetText();
string signLower = sign.ToLower();
int pos = signLower.IndexOf("proc");
if (pos == -1)
{
return;
}
bool PosInSpaceBefProcName = false;
bool PosProcName = false;
string spname = string.Empty;
for (int i = pos; i < sign.Length; ++i)
{
char c = sign[i];
if (PosInSpaceBefProcName == false)
{
if (SPSignature.IsWhitespace(c) == false)
{
continue;
}
else
{
PosInSpaceBefProcName = true;
}
}
else if (PosInSpaceBefProcName && PosProcName == false)
{
if (SPSignature.IsWhitespace(c))
{
continue;
}
else
{
spname += c;
PosProcName = true;
}
}
else if (PosProcName)
{
if (SPSignature.IsWhitespace(c) == false && c != '(')
{
spname += c;
}
else
{
break;
}
}
}
PastedEvent(spname.Trim());
}
}
base.WndProc(ref m);
}
