private static void AddPackage(DataRow row, string packageKey, DatabaseArgument argument)
{
if (packageKey != null)
{
argument.PackageName = row[packageKey].ToString();
}
}
public void CreateTable(string TableName, DatabaseArgument argument)
{
string sql = "create table if not exists " + TableName + argument.GetDatabaseTypeSql();
var command = new SQLiteCommand(sql, conn);
command.ExecuteNonQuery();
}
private static void ApplyColumnType(string columnType, DatabaseArgument argument)
{
switch (columnType)
{
case "SQL_PARAM_INPUT":
argument.In = true;
return;
case "SQL_PARAM_INPUT_OUTPUT":
argument.In = true;
argument.Out = true;
return;
case "SQL_PARAM_OUTPUT":
argument.Out = true;
return;
case "SQL_RETURN_VALUE":
argument.Ordinal = -1;
return;
case "SQL_RESULT_COL":
//db2 returns result columns?
break;
}
}
private void WriteArgument(DatabaseArgument argument)
{
_cb.AppendLine("");
_cb.AppendLine("//" + argument.Name + " " + argument.DatabaseDataType);
string s = string.Format(CultureInfo.InvariantCulture,
"AddWithValue(cmd, \"{0}\", {1});",
argument.Name, SprocLogic.ArgumentCamelCaseName(argument));
if (!argument.Out)
{
//normal in parameters
_cb.AppendLine(s);
return;
}
// output and input-output parameters.
var pName = "p" + argument.NetName;
var isRefCursor = string.Equals("REF CURSOR", argument.DatabaseDataType, StringComparison.OrdinalIgnoreCase);
if (argument.In)
{
//input output
_cb.AppendLine("var " + pName + " = " + s);
}
else
{
//just output
if (isRefCursor)
{
_cb.AppendLine("var " + pName + " = (OracleParameter)cmd.CreateParameter();");
}
else
{
_cb.AppendLine("var " + pName + " = cmd.CreateParameter();");
}
_cb.AppendLine(pName + ".ParameterName = \"" + argument.Name + "\";");
_cb.AppendLine("cmd.Parameters.Add(" + pName + ");");
}
_cb.AppendLine(pName + ".Direction = ParameterDirection." + (argument.In ? "InputOutput" : "Output") + ";");
// you may need DbType on output parameters
if (isRefCursor)
{
_cb.AppendLine(pName + ".OracleType = OracleType.Cursor;");
return;
}
var dt = argument.DataType;
if (dt == null)
{
_cb.AppendLine(pName + ".DbType = DbType.Object;");
return;
}
var t = dt.GetNetType();
_cb.AppendLine(pName + ".DbType = DbType." + Type.GetTypeCode(t) + ";");
}
public void ProcedureTest()
{
var schema = PrepareModel();
var procedure = new DatabaseStoredProcedure {
Name = "SelectCategory"
};
var argument = new DatabaseArgument
{
Name = "p1",
DatabaseDataType = "VARCHAR",
Length = 10,
DataType = DataTypeConverter.FindDataType("VARCHAR", schema.DataTypes, SqlType.SqlServer, null),
In = true,
};
procedure.Arguments.Add(argument);
var rs = new DatabaseResultSet();
var resultColumn = new DatabaseColumn {
Name = "Output", DbDataType = "VARCHAR"
};
DataTypeConverter.AddDataType(resultColumn);
rs.Columns.Add(resultColumn);
procedure.ResultSets.Add(rs);
schema.StoredProcedures.Add(procedure);
var directory = TestHelper.CreateDirectory("MySprocTest");
const string @namespace = "MySprocTest";
var settings = new CodeWriterSettings
{
Namespace = @namespace,
CodeTarget = CodeTarget.Poco,
WriteStoredProcedures = true
};
var target = new CodeWriter(schema, settings);
target.Execute(directory);
var procedures = directory.GetDirectories("Procedures").FirstOrDefault();
if (procedures == null)
{
Assert.Fail("Could not find Procedures subdirectory");
}
var files = procedures.GetFiles("*.cs");
var products = files.FirstOrDefault(f => f.Name == "SelectCategory.cs");
Assert.IsNotNull(products, "Should have written SelectCategory class for SelectCategory procedure");
var category = files.FirstOrDefault(f => f.Name == "SelectCategoryResult.cs");
Assert.IsNotNull(category, "Should have written SelectCategoryResult class to the result of the sproc");
}
public static string ArgumentCamelCaseName(DatabaseArgument argument)
{
var name = argument.NetName;
if (string.IsNullOrEmpty(name))
{
name = NameFixer.ToPascalCase(argument.Name);
argument.NetName = name;
}
return(Char.ToLowerInvariant(name[0])
+ (name.Length > 1 ? name.Substring(1) : string.Empty));
}
private static DatabaseArgument AddArgumentToList(List <DatabaseArgument> list, string argName)
{
var existing = list.Find(delegate(DatabaseArgument arg) { return(arg.Name == argName); });
if (existing == null)
{
DatabaseArgument argument = new DatabaseArgument();
argument.Name = argName;
list.Add(argument);
return(argument);
}
return(existing);
}
private static void AddInputParameter(DbParameter parameter, DatabaseArgument argument)
{
if (argument.DataType.IsString)
{
parameter.DbType = DbType.AnsiString;
//a string "1" seems better than a letter (TO_NUMBER, CONVERTs etc)
parameter.Value = "1";
}
else if (argument.DataType.IsNumeric)
{
parameter.Value = 1;
}
else if (argument.DataType.IsDateTime)
{
parameter.Value = DateTime.Now;
}
}
private static void AddInOut(DataRowView row, string inoutKey, DatabaseArgument argument)
{
if (inoutKey == null)
{
return;
}
string inout = row[inoutKey].ToString();
if (inout.Contains("IN"))
{
argument.In = true;
}
else if (inout.Contains("OUT"))
{
argument.Out = true;
}
}
private static void AddParameter(DbParameter parameter, DatabaseArgument argument)
{
parameter.ParameterName = argument.Name;
if (argument.In && argument.DataType != null)
{
//add dummy data
AddInputParameter(parameter, argument);
}
if (argument.Out && argument.In)
{
parameter.Direction = ParameterDirection.InputOutput;
}
if (argument.Out)
{
AddOutputParameter(parameter, argument);
}
}
private static void AddOutputParameter(DbParameter parameter, DatabaseArgument argument)
{
parameter.Direction = ParameterDirection.Output;
if (argument.DataType != null)
{
if (argument.DataType.IsString)
{
parameter.DbType = DbType.AnsiString;
}
else if (argument.DataType.IsNumeric)
{
parameter.DbType = DbType.Int32;
}
else if (argument.DataType.IsDateTime)
{
parameter.DbType = DbType.DateTime;
}
}
if (argument.DatabaseDataType == "REF CURSOR")
{
AddRefCursorParameter(parameter);
}
}
