最近我们在写一个进销存系统,老师提供给我们的教材中建立了与数据库字段名对应的类并设置属性是数据库字段名,由于一个类一个类的写很麻烦,所以不如写个程序生成一下cs文件
所以我们需要读取数据库表:
SELECT NAME as tbName FROM SYSOBJECTS WHERE XTYPE='U'
获取到表名后可以去获取表的字段名和类型
select syscolumns.name,systypes.name as type from syscolumns,systypes where syscolumns.xusertype=systypes.xusertype
and syscolumns.id=object_id('表名')
先看一下效果吧:
在读取完表以后就可以读取字段然后写入文件,代码如下:
private void btnOk_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(txtSql.Text.Trim());
try
{
conn.Open();
cmd = new SqlCommand("SELECT NAME as tbName FROM SYSOBJECTS WHERE XTYPE='U'",conn);
SqlDataReader sdr = cmd.ExecuteReader();
string[] tbNames = gettbNames(sdr);
ToClass(tbNames);
}
catch (Exception exception)
{
Console.WriteLine(exception);
throw;
}
cmd.Dispose();
conn.Dispose();
}
private string[] gettbNames(SqlDataReader sdr)
{
List<string> names = new List<string>(5);
while (sdr.Read())
{
names.Add(sdr["tbName"].ToString());
}
sdr.Close();
return names.ToArray();
}
private void ToClass(string[] names)
{
foreach (string name in names)
{
cmd.CommandText =
"select syscolumns.name,systypes.name as type from syscolumns,systypes where syscolumns.xusertype=systypes.xusertype " +
"and syscolumns.id = object_id('" + name + "')";
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds, name);
table = ds.Tables[name];
ToFile(name,table);
}
}
private void ToFile(string name, DataTable dataTable)
{
FileStream file = new FileStream(path+"\\"+name+".cs",FileMode.Create);
StreamWriter writer = new StreamWriter(file);
StringBuilder builder = new StringBuilder();
builder.AppendLine("namespace " + txtName.Text);
builder.AppendLine("{");
builder.AppendLine("class " + name);
builder.AppendLine("{");
for (int i = 0; i < dataTable.Rows.Count; i++)
{
if (dataTable.Rows[i]["type"].ToString().Equals("nvarchar") || dataTable.Rows[i]["type"].ToString().Equals("varchar"))
{
builder.AppendLine("private string " + dataTable.Rows[i]["name"]+";");
}
if (dataTable.Rows[i]["type"].ToString().Equals("datetime"))
{
builder.AppendLine("private DateTime " + dataTable.Rows[i]["name"]+";");
}
if (dataTable.Rows[i]["type"].ToString().Equals("int"))
{
builder.AppendLine("private int " + dataTable.Rows[i]["name"]+";");
}
}
builder.AppendLine("}");
builder.AppendLine("}");
writer.Write(builder);
writer.Close();
file.Close();
}
代码中的判断类型可以根据自己的需要添加,然后将生成的文件引入项目中,在排版一下就可以了(ctrl+A,ctrl+K,ctrl+F)
这样就不用一个一个的去写了
然后封装类(ctrl+R,ctrl+E)